outcome of SQL INSERT statement not working

[Pete64]

I got help yesterday with a problem and got help working it out or so I thought, heres whats happening

OK here it is the code, test this and tell me why it won't work it makes no sense. Basically this is executed on a button click from a form and the values serviceno and start are normally taken from a listbox but for testing purposes heres already set numbers.
within this Access database includes a table called -

table - Timesheet Header
and it has three fields
field datatype
Flag yes/No
SERVICE_COMPANY_NO text
PERIOD_STARTING Date/Time

It includes this data
flag SERVICE_COMPANY_NO PERIOD_STARTING
FALSE 0989 11/09/99
FALSE 7865 02/02/98
FALSE 4555 09/08/01

form - frmflag set an onclick event and use following code:
Dim serviceno As String
Dim start As Date
serviceno = "7865"
start = "02/02/98"


DoCmd.RunSQL "UPDATE [Timesheet Header] SET Flag = True " & _
"WHERE SERVICE_COMPANY_NO='" & serviceno & "' " & _
"AND PERIOD_STARTING = #" & start & "#;"

basically this SQL statement is supposed to update my field flag to true where service_company_no = variable serviceno set in code declaration and period_starting = start set in code.

OK heres everything you need to replicate my access database as it is, yeah It will work but only with the one value set serviceno = "7865" and
start = "02/02/98" the one that is initally set. try changing the values in the code to other record values from the table and see if it works it don't why I don't know.

please help me understand this somebody I've tried fer hours ; )
and I'll include an attached example press on both toggle buttons called select visible only and turn flag on buttons and then press OK to get form to perform code. on the frmform

 

[namliam]

First you attached an old DB which didnt work properly (the one you attached in another thread and i modified so it works ...)

This works just fine! exept you are probably going "up the creek" on the date

Access stores the date in mm/dd/yy format despite whatever format you put on it for input. and despite your local settings...
when searching for 11/09 you mean 11 sep where Access says 9 nov. Try using :
serviceno = "0989"
start = "09/11/99"

and it will work just perfectly fine ....

Regards

P.s. you could have posted this in the other thread as well, without starting a new thread....

 

[Pete64]

thanks for your help

I just thought it would ave accepted my default local settings, I see it all clearly now thanks for all your help. It was just the fact that one record worked whereas the rest didnt but it was "02/02/11" so I understand.

So how could I overcome this date problem?
thanks for all your patience, I am quite new to databases but am learning

 

[Rich]

Format([YourDate],"mm/dd/yyyy")

 

[Pete64]

thanks rich

you guys are a big help

and maybe one day very soon I'll will be able to answer one of your questions.