Changing dateserial statement into format() 2

[ponderdj]

Hello,

I am testing a database that is being changed from an access backend to an oracle backend (access frontend). I have found that is bombs out everywhere Dateserial is used, so I have been changing statements to format(), and it has been working until I changed this:

'Combine MonYr and Day fields to get TSOReceivedDate field
    'DoCmd.Echo False, "Updating TSOReceivedDate field..."
    'Set qd = db.CreateQueryDef("", "UPDATE [tblIncomingMessages]" & _
    '"Set TSOReceivedDate = DateSerial([Year],IIf([Month]='JAN',1,IIf([Month]='FEB',2,IIf([Month]=
'MAR',3,IIf([Month]='APR',4,IIf([Month]='MAY',5,IIf([Month]='JUN',6,IIf
([Month]='JUL',7,IIf([Month]='AUG',8,IIf([Month]='SEP',9,IIf([Month]=
'OCT',10,IIf([Month]='NOV',11,IIf([Month]='DEC',12,0)))))))))))),[Day]) 
" & _
    '"WHERE [tblIncomingMessages].TSOReceivedDate is null")
    'qd.Execute
    'qd.Close

To this:

'Combine MonYr and Day fields to get TSOReceivedDate field
DoCmd.Echo False, "Updating TSOReceivedDate field..."
    strSql = "UPDATE [tblIncomingMessages] Set TSOReceivedDate = Format([Day], 'dd') & '/' & Format([Month], 'mm') & '/' & Format([Year], 'yyyy') WHERE [tblIncomingMessages].TSOReceivedDate is null"
    DoCmd.RunSQL (strSql)

The date is entered into the TSOReceivedDate, but it's wrong.

Day-'10' Month-'DEC' Year-'2004' are entered as 9/12/1905

Can anyone offer any suggestions?

 

[Golom]

You don't want to format them as "dd", "mm", and "yyyy" because you are already creating that format with the concatenated format statements and "/" characters. When you tell "Format" to use "dd", "mm" or "yyyy" it assumes that its argument must be a date and, if it isn't, converts it to a date which goes like this

cDate(10) = "January 9, 1900"
cDate(12) = "January 11, 1900"
cDate(2004) = "June 26, 1905"

You need rather

Set TSOReceivedDate = Format([Day], '00') & '/' & Format([Month], '00') & '/' & Format([Year], '0000')

 

[PHV]

Set TSOReceivedDate = [Day] & '/' & [Month] & '/' & [Year]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ponderdj]

Golom,

I'm not sure what you mean with Cdate, but your suggestion worked immediately! I've been frustrated with this all day.

Thank You,
-David

 

[ponderdj]

PHV,

Your solution worked as well.

Thanks,
David