Update query problem

[bjackson]

I am trying to update 4 fields in a table using a update query but cant seem to get it right.I keep getting an error message "missing operator in the expression 8:17M" or if i leave the TimeStr out of the update the date saved in DateStr is 30/12/1899

Dim TimeStr
Dim DateStr As Date
Timestr = Format(Me.DateReceived, "h:nn:AM/PM")
DateStr = Format(Me.DateReceived, "dd/mm/yy")

DoCmd.RunSQL ("UPDATE PurchaseItems SET PurchaseItems.ItemReceived =True,PurchaseItems.ReceivedBy =" & Me.ReceivedBy _
& ",PurchaseItems.DateReceived = " & DateStr _
& ",PurchaseItems.TimeReceived =" & Timestr _
& " WHERE (((PurchaseItems.ItemKeyNumber)=" & Me.IdNum & "));")

Can anybody see what i am doing wrong

 

[Mile-O]

PurchaseItems.DateReceived = " & DateStr _

If you've dimensioned DateStr as a date then you need to use the date delimiters.


i.e.

PurchaseItems.DateReceived = #" & DateStr & "#

Other things:

Dim TimeStr

This dimensions TimeStr as a Variant which eats up more memory and slows procedures down.


Dim DateStr As Date
DateStr = Format(Me.DateReceived, "dd/mm/yy")

You have dimensioned DateStr as a date but the Format() function returns a string which you can't assign.


Why though, do you need two fields to show the time received anyway? You can quite easily combine this as one Date/Time field. For example, your two fields - let's say the date received is today (#19-07-2004#)and the time received is now (#11:42:00#) you are going to have two fields with the following values:

#19/07/2004 00:00:00#

and

#30/12/1899 11:42:00#

when, ideally, you'd want #19/07/2004 11:42:00#


Also, why are you doing this in VBA when it's more efficient to create a pre-compiled query?

 

[bjackson]

Thanks Mile-O-Phile

its early morning this side of the world so i havent had the opportunity
to try your suggestions,but to answer your questions

1.i use 2 fields because i am easily confused when working with date/time fields.I know i should only use one.maybe its time i applied myself and start using 1 field.


2.pre compiled querys may be more effecient,but if an application has
a lot of querys then its not as easy to find/remember which query does what.This is only a personal preference
I have also found some compiled querys do not execute as quickly as Vba does in a network enviroment and have found the speed difference significant,although i dont understand why

regards
Bjackson