Replies

Mark,

not a solution for your specific problem, but you might get a clue from Access Archon column 193. I successfully used this for single address display, in the UK. Are you fixed on Google maps? There are commercial packages that will do what you want (e.g. Microsoft’s own MapPoint). Integrating non-Microsoft products with Access may be a problem.

Regards,

Jules

Microsoft says: When a database is deleted, the files and their data are deleted from the disk on the server. When a database is deleted, it is permanently removed and cannot be retrieved without using a previous backup.

http://msdn.microsoft.com/en-us/library/ms189278.aspx

There are, of course, utilities that can recover ‘deleted’ files, and the only way to be sure would be to overwrite the area on the disk(s) several times with rubbish.

Look at the efforts the Government has taken to destroy the National Identity Register:

http://www.computerweekly.com/Articles/2011/02/10/245363/ID-card-database-is-destroyed.htm

Regards,

Jules

I stand corrected!

Jules

Sorry, I cannot think of a way to do this in a query. Unless others can suggest a better way, you’re looking at a simple bit of code.

1. Identify a recordset to hold the result (e.g. a table, remember to empty it before starting)
2. Open a recordset to show the source data.
3. Go to the first (only?) record of your source data, and the first field.
4. Read the value.
5. Append the field name and the value to the temporary table (remember to update if using a recordset, but I’d use an Append SQL statement)
6. Move to the second field, and repeat steps 4 and 5. Continue until you run out of fields.

Close your recordset(s), and open the pie chart which is based on the temporary table.

Hope this helps,

Jules

Corey,

sorry, I’m having trouble getting my head around the result you are aiming for. I can offer a quick suggestion, though.

Several of your fields are actually totals subqueries, e.g. 3MonthRank: (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]). You do not have a primary key on the table, but I assume that OMNI# holds unique values.

Why not have this as a separate query grouped by OMNI#, and pull in the result? You could do the same for the other subqueries. It is easier to debug when steps are broken down.

This might be a first step to a solution.

Regards,

Jules

Corey,

me again! There are several options, and using the properties of the field is probably the least flexible. You could use a Round() function, e.g. Round([fieldname],1). Or, you could use the Format() function. I’d suggest Round() for greater accuracy.

If you want to look at Format(), check out the VBA help rather than the Access help – open a code window and then open help.

Below is an extract from the VBA help.

Regards,

Jules

Different Formats for Different Numeric Values (Format Function)

A user-defined format expression for numbers can have from one to four sections separated by semicolons. If the format argument contains one of the named numeric formats, only one section is allowed.

If you use The result is
One section only The format expression applies to all values.
Two sections The first section applies to positive values and zeros, the second to negative values.
Three sections The first section applies to positive values, the second to negative values, and the third to zeros.
Four sections The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null values.

The following example has two sections: the first defines the format for positive values and zeros; the second section defines the format for negative values.

“$#,##0;($#,##0)”

If you include semicolons with nothing between them, the missing section is printed using the format of the positive value. For example, the following format displays positive and negative values using the format in the first section and displays “Zero” if the value is zero.

“$#,##0;;Zero”

Hi!

have you considered a Crosstab query?

Regards,

Jules

Hi!

When setting up the connection, the ‘select tables’ dialog box has a box for ‘save password’. Tick the box. The password is then saved along with the other connection details.

Regards,

Jules

Dave,

there are many possible solutions. Probably the simplest is to pass the ID as part of a Where parameter. If you look at the options for Docmd.OpenReport, you will see one is a WhereCondition. Fill this with a string such as “[ID]=29”, and it should work.

Regards,

Jules

Niven,

the traditional way to ‘find unmatched’ is to link the two tables with an equals join, then set a criteria on the second table primary key of ‘is null’. This returns records from the first table without a matching record in the second table. Cominig from a Microsoft Access background with a smattering of SQL Server, this is the approach I’d take. The ‘not in (sql statement)’ has its uses but at first glance I would not have said this was one of them. By grouping and using a max(), you seem to be muddying the waters.

Got to rush, hope this helps a bit,

Jules

Oops! While cycling I realised I had omitted a key line. You need to set strURL to the address of the web site, before using the variable.

Sorry about that, but I expect everyone spotted the (non-deliberate) mistake.

Jules

Hi Luke!

I think you will need to drop an ocxWebBrowser control onto your form. Then set the URL in code (based on code by Helen Feddema) :

On Error GoTo ErrorHandler

dim appBrowser As Object
dim strURL as string

Set appBrowser = Me![ocxWebBrowser]
Me![ocxWebBrowser].Navigate strURL

ErrorHandlerExit:
Set appBrowser = Nothing
Exit Sub

ErrorHandler:
MsgBox “Error No: ” & Err.Number _
& ” in Form_Current procedure; ” _
& “Description: ” & Err.Description
Resume ErrorHandlerExit

Regards,

Jules

Hi!

It’s not clear if the employeee is entitled to overtime based on daily or weekly rates, e.g. does overtime worked on a Sunday get paid at a greater rate that overtime worked on a Tuesday. I have assumed different rates.
Try thinking of this problem in spreadsheet terms. Entries would be grouped per week, and the hours worked totalled. If the total hours for the week equals or exceeds 40, then for each day of that week one column would show the hours worked, the next would show the normal hours for that day, a third would show the difference (Overtime). This could then be multiplied by the appropriate overtime rate to give an amount due.

All of this can be worked out using queries, and Append/Update queries to write the results to a table.

This is very simplistic, and in real life you would probably group the normal weekday hours together rather than show them by day.

But….. there are a myriad of payroll systems out there, that can handle these calculations. Might you be better using an off-the-shelf solution rather than reinventing the wheel?

Regards,

Jules

Tom,

you want a simple function along the lines of

if curdate =>cdate(“01/07/” & year(curdate)) then

[indent]Return year(curdate)+1[/indent]

else

[indent]return year(curdate)[/indent]

end if

I am sure you can tidy this up and get the syntax right! Could even be done with an IIF().

Hope this helps,

Jules

Pete,

for someone new to access, there are several great sites around (including, of course, this one!). You could do worse than look at Access Archon; Item 6 may be relevant.

If you want to keep it really simple, in the After Update of the combo box, use:

[indent]If nz(cmbWhatever,0) 0 then

[indent][/indent][indent][/indent]targetcontrolname=DLookup(x,y,z).

end if[/indent]

Dlookup is slow, but relatively easy to use. Check the Help. Just remember that speech marks are needed.

Or, if the value really is predefined and will not change, replace the DLookup with a Select Case statement.

[indent]Select Case nz(cmbWhatever,0)

Case x

[indent][/indent]targetcontrolname=xxx

Case y

[indent][/indent]targetcontrolname=xyz

Case Else

[indent][/indent]targetcontrolname=null

End Select[/indent]

Regards,

Jules