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

 

[Pat Hartman]

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

- this could happen only if the table size of one or more of the tables in the query changed dramatically and the db was not compacted recently. When the db is compacted, all querydefs are re-evaluated to to recalculate a new access plan. It may be that when the query was first saved, Jet determined that it would be best to do a full table scan because the number of rows in a table was small but the number of rows grew dramatically once real data was added to the db thereby making an index scan more efficient. Since VBA strings are reanalized EVERY time they run, the table stats would always be current.

Come up with a naming scheme that allows you to keep track of your querydefs. You can't effectively secure a db (should you ever need to) unless you use stored querydefs with the "WITH OWNER ACCESS" option.