Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to make it so that when i push a button it opens a report and
executes this update.

1.
DoCmd.RunSQL "UPDATE Run SET Print_Date = Date(), Print_Time = Time() WHERE
Type = 'PAPS' AND ID = MAX(ID); "

2.
DoCmd.RunSQL "UPDATE Run SET Print_Date = Date(), Print_Time = Time() WHERE
TYPE = 'PAPS' AND Print_Date = ' '; "

Now I am getting a couple Errors
1. "Cannot have aggregate function in WHERE clause (ID=Max(ID))."
2. "Data Type Mismatch in criteria expression"

Can someone see what it is that is not functioning properly

thanks in advance
Herhsal
 
I am trying to make it so that when i push a button it opens a report and
executes this update.

1.
DoCmd.RunSQL "UPDATE Run SET Print_Date = Date(), Print_Time = Time() WHERE
Type = 'PAPS' AND ID = MAX(ID); "

2.
DoCmd.RunSQL "UPDATE Run SET Print_Date = Date(), Print_Time = Time() WHERE
TYPE = 'PAPS' AND Print_Date = ' '; "

Now I am getting a couple Errors
1. "Cannot have aggregate function in WHERE clause (ID=Max(ID))."
2. "Data Type Mismatch in criteria expression"

Can someone see what it is that is not functioning properly
Click to expand...

Several problems here. For one thing, Access Date/Time fields are designed to
store date and time together. I'd suggest using a Date/Time field Print_Time
and just setting it to Now() (current date and time). If you want to stick
with the two fields you need to extract the Max expression from within the
query:

DoCmd.RunSQL "UPDATE [Run] SET Print_Date = Date(), Print_Time = Time() WHERE
[Type] = 'PAPS' AND ID = " & DMax([ID], [Run]) & "; "

For the second query, a Date is not a String, so comparing it to ' ' will
indeed give a data type mismatch. To find records with nothing in the
Print_Date field use

AND Print_Date IS NULL;

The two queries really seem to be at odds with one another, though! How can
you rely on the maximum value of ID finding the right record? If you have a
multiuser system, you have no way to know that some other users haven't added
three or four records after the ID you're working with.


John W. Vinson [MVP]
 
Thanks for the advice John and yeah the whole date thing makes sense and
works fine thanks again for your help

John W. Vinson said:
I am trying to make it so that when i push a button it opens a report and
executes this update.

1.
DoCmd.RunSQL "UPDATE Run SET Print_Date = Date(), Print_Time = Time() WHERE
Type = 'PAPS' AND ID = MAX(ID); "

2.
DoCmd.RunSQL "UPDATE Run SET Print_Date = Date(), Print_Time = Time() WHERE
TYPE = 'PAPS' AND Print_Date = ' '; "

Now I am getting a couple Errors
1. "Cannot have aggregate function in WHERE clause (ID=Max(ID))."
2. "Data Type Mismatch in criteria expression"

Can someone see what it is that is not functioning properly
Click to expand...

Several problems here. For one thing, Access Date/Time fields are designed to
store date and time together. I'd suggest using a Date/Time field Print_Time
and just setting it to Now() (current date and time). If you want to stick
with the two fields you need to extract the Max expression from within the
query:

DoCmd.RunSQL "UPDATE [Run] SET Print_Date = Date(), Print_Time = Time() WHERE
[Type] = 'PAPS' AND ID = " & DMax([ID], [Run]) & "; "

For the second query, a Date is not a String, so comparing it to ' ' will
indeed give a data type mismatch. To find records with nothing in the
Print_Date field use

AND Print_Date IS NULL;

The two queries really seem to be at odds with one another, though! How can
you rely on the maximum value of ID finding the right record? If you have a
multiuser system, you have no way to know that some other users haven't added
three or four records after the ID you're working with.


John W. Vinson [MVP]
Click to expand...
 
Back
Top