Format now() in VBA

[Gunilla]

Hi you all
Is there a way to code now() in VBA.
To be according to the remote database I need it to be in the format dd/mm/yyyy hh:mm:ss (03/03/2010 09:09:09 AM)
I can get the format working in a query but the problem is that my code is looking for a special field with the now() value and that field will read
3/3/2010 9:9:9 AM and the remote database will not accept that.
Anyone have some suggestions?
Gunilla

 

[boblarson]

You can:

Something like:


X = Format(Now,"dd/mm/yyyy hh:nn:ss")

notice how for MINUTES I used nn and not mm. nn will work more consistently for minutes.

 

[Gunilla]

Thank you, but it didn't work. This is how it shows
4/20/2010 4:21:18 PM
and I have to have two digits for month and day. Do you think it is a bug or is it just
Access telling me "I do it my way",
Gunilla

 

[boblarson]

When you say it "didn't work" can you show exactly what you tried and tell me where you tried it. Did you try setting the value to a text box on a form? Did you try using it in a query? If in a query what is the whole SQL statement of the query? etc.

 

[Gunilla]

I have created a form where I have a button that runs a code to transfer information to a text file.
When there are records that should be transferred the batch number, that is the date in Now format, is entered. This is where I used your suggestion
v_Batch = Format(Now, "mm/dd/yyyy hh:nn:ss")
This is what I had before
v_Batch =Now()
To get around the problem I have a query where I have formatted the fields.

SELECT Format([MR],"0000000") AS PMR, Format([LabLocation],"000") AS LabLoc, Billing.NPI, Format([TAKEN],"mm/dd/yyyy") AS [Date], Billing.TEST_NAME, Format([Batch],"mm/dd/yyyy hh:mm:ss") AS Bat, Format([ID],"0000000000") AS Entry
FROM Billing
ORDER BY Format([ID],"0000000000");

in this the formatting works but I can't get it exported to the text file because the Batch number is not like v_Batch
Unfortunately I cannot send you the database as it contains restricted material, but if you can help me with this I would really appreciate it.
Gunilla