Print # format

[rbs]

Hi Everyone,

I have a module creating a dat file in which it writes records from my query without using delimiters.

I have a problem in that it is exporting date fields in the wrong format. Within the query a date is set in the format yyyymmdd. But when the data is transfered it comes through as dd/mm/yyyy.

Can anyone tell me how to fix this?

Thanks in advance.


Natalie.

 

[SydneyGeek]

You have to explicitly set the date format in the export, or Access will default to your regional setting.

Assuming you have [MyDate] as a field, you could try ...
declaring MyFormattedDate as a String.
MyFormattedDate = Format$([MyDate],"yyyymmdd")
... and exporting the string instead of the date.

HTH
Denis

 

[rbs]

Thanks for your help...

I have set up the module as follows:

........
Dim SellInTxnDate As String
........

Set db1 = CurrentDb
Set recset1 = db1.OpenRecordset("PURCHASES DAILY ALL QUERY")

With recset1

Do Until .EOF

.......
SellInTxnDate = !Format$([Txn Date], "yyyymmdd")
.......

Open Name For Append As #1

Print #1, ..... & SellInTxnDate & .....
Close #1 'close the text file

.MoveNext
Loop
.Close

End With


It comes back saying

Compile Error
External Name not defined refererring to the line:

SellInTxnDate = !Format$([Txn Date], "yyyymmdd")


Can you help?

 

[SydneyGeek]

Try changing this...
SellInTxnDate = !Format$([Txn Date], "yyyymmdd")
to this...
SellInTxnDate = Format$(![Txn Date], "yyyymmdd")
or this...
SellInTxnDate = Format$([Txn Date], "yyyymmdd")
Does that help?

Putting the exclamation in front of Format tells Access to look for a field, where you are referring to a function here
Denis

 

[rbs]

Thanks!

SellInTxnDate = Format$(![Txn Date], "yyyymmdd")

Worked