Date Format Not Working as I want 1

[GummowN]

As I want started off as properly but it is probably something I have missed.

I have the following code, but if the date is before the 12th day of the month it swaps to US format dtmMax, so doesnt work.

Public Function fncGetMaxStatus(intRequest As Integer) As Integer
Dim dtmMax As Date
strSQL = "SELECT Max(RequestStatusDate) AS MaxDate " & _
"FROM tbl_RequestStatus " & _
"WHERE Request= " & intRequest & ";"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If IsNull(rst!maxdate) Then
fncGetMaxStatus = 0
Else
dtmMax = rst!maxdate
'MsgBox dtmMax
strSQL = "SELECT Status " & _
"FROM tbl_RequestStatus " & _
"WHERE Request= " & intRequest & _
" AND format(RequestStatusDate, 'dd/mm/yyyy hh:nn:ss') = #" & Format(dtmMax, "dd/mm/yyyy hh:nn:ss&quot & "#;"
Set rst = dbs.OpenRecordset(strSQL)
'MsgBox strSQL
If rst.EOF Then
fncGetMaxStatus = 0
Else
fncGetMaxStatus = rst!Status
End If
End If
rst.Close
Set dbs = Nothing
End Function

I would have thought that defining the format for both the field and the passed parameter then it should work. The alternative is to produce a query, and pass then pass the parameters to that object, but I rather like minimizing the number of objects in a database.

All suggestions gratefully received

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[nicsin]

Hi,

in the format function use:

'dd/mmm/yyyy hh:nn:ss'

 

[KenReay]

Hi

I assume you are in the UK?

In SQL strings bulit in code, Acess (well Jet) ALWAYS uses US format for dates (ie mm/dd/yy) so your problem is with the snippits of code


"WHERE Request= " & intRequest & _
" AND format(RequestStatusDate, 'dd/mm/yyyy hh:nn:ss') = #" & Format(dtmMax, "dd/mm/yyyy hh:nn:ss&quot & "#;"

you need to make it


"WHERE Request= " & intRequest & _
" AND format(RequestStatusDate, 'mm/dd/yyyy hh:nn:ss') = #" & Format(dtmMax, "mm/dd/yyyy hh:nn:ss&quot & "#;"

or


"WHERE Request= " & intRequest & _
" AND format(RequestStatusDate, yyyy/mm/dd hh:nn:ss') = #" & Format(dtmMax, "yyyy/mm/dd hh:nn:ss&quot & "#;"

I have left in the Hours minutes second son the assumption you have a good reason for going to this level of exactness in time

Regards

Ken Reay




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GummowN]

Thanks for the suggestions so far but neither worked.

I am guessing that it is not that the formatting is working incorrectly but that when it spurts out dtmMax from the first recordset it makes the assumption then what is MM and what is DD.

Any ideas on forcing this out in a set format

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[KenReay]

Hi

OK looking more closely at you SQL, you need to remember that the Format() function returns a string not a date type

so try

" AND RequestStatusDate = #" & Format(dtmMax, "mm/dd/yyyy hh:nn:ss&quot & "#;"


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GummowN]

Nice try, but no that doesnt work either.

I have tried all permutations of the format methodology, so I guess I might have to look for another way of doing this.

Any more guesses would be great. It appears that using an extracted date from a recordset and passing it to another is just a step too far for Access.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[GummowN]

Here is my workround:

Instead of getting the max of the date field, I get the max of the autonumber field. As each record is added sequentially as the autonumber increase the date therefore does also. If I take the max of the autonumber field (an integer) I dont get any formatting problems!!

Not only does it work, but my headache has vanished!!

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[PHV]

GummowN, and what about a single SQL statement like this ?[tt]
strSQL = _
"SELECT Status " & _
"FROM tbl_RequestStatus " & _
"WHERE Request = " & intRequest & _
" AND RequestStatusDate = (" & _
"SELECT Max(RequestStatusDate) " & _
"FROM tbl_RequestStatus " & _
"WHERE Request=" & intRequest & ");"[/tt]
No need of intermediate variable at all.

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

 

[RichD]

I had exactly the same problem and resolved it by using ' (single quote) in place of #.

To use KenReay's example I changed
" AND RequestStatusDate = #" & Format(dtmMax, "mm/dd/yyyy hh:nn:ss") & "#;"

to

" AND RequestStatusDate = '" & Format(dtmMax, "mm/dd/yyyy hh:nn:ss") & "';"

Thanks Ken for putting me on the right trck.

Rich





Bespoke and off-the-shelf Access solutions

http://www.blsoftware.co.uk