Format Field while retaining Datatype (1 Viewer)

Spackle · Jul 12, 2018

I've been beating my head against this one for a while now, and I'm sure I'm not the first person on the planet with this issue.

I'm using a query to create a table. I then perform some VBA manipulation before outputting the modified table to an excel spreadsheet. The issue I have is that one field needs to be a date datatype so I can compare to other dates, but the spreadsheet needs this field in the format dd.mm.yyyy as it is then input into another program.

If I use 'format' in the query, the field gets changed into a text field, so my comparisons don't work, or I end up with the output dates in the wrong format.

Can I somehow do this all within the query, or can I change either the formatting or the datatype of the field after the fact, just before I output to excel?

Gasman · Jul 12, 2018

I would have thought you just export as date. Excel holds it as date. How it is formatted is up to the user, it just has to be a date.?

Ranman256 · Jul 12, 2018

A query can only guess at the specific field type.
Instead of make table query, use a pre build table ,with correct field types,
Then empty table and append qry.

Spackle · Jul 12, 2018

So there's no way to do this dynamically? I can use cDate to set the datatype in the query. I can use format to get the numbers in the right order (using 'dd.mm.yyyy'), but I can't use them both at once?

isladogs · Jul 12, 2018

If you really need to do both, then use

Code:

CDate(Format(DateField, "dd.mm.yyyy"))

But if that's your default date format, that's how Access will show the dates without using date formatting

Spackle · Jul 12, 2018

ridders said:
If you really need to do both, then use

Code:

CDate(Format(DateField, "dd.mm.yyyy"))

But if that's your default date format, that's how Access will show the dates without using date formatting

Thanks Ridders. I tried that, but my date field comes out blank on the table.:banghead:

Code:

"SELECT CDate(Format([ActivityDate], ""dd.mm.yyyy"")) AS [DATE]" etc

I'm generating my query by creating it as a string, which is why I have double quotes, then using .CreateQueryDef to run it.

If you're suggesting that this should be possible in theory, is it that I've got the syntax wrong somewhere?

Mark_ · Jul 12, 2018

Spackle said:
but the spreadsheet needs this field in the format dd.mm.yyyy

Is the spreadsheet doing something... odd... that it needs it in a specific format? Is the spreadsheet set up to treat it as a string for some ungodly reason?

Both access and excel store date/times the same way; it is a number. Neither stores "01.01.2018", both would store 43101. Unless there is something rather wonky going on you should be able to export without formatting.

Spackle · Jul 12, 2018

Oh, it's wonky alright. I need the spreadsheet to be in this very specific format because it is then uploaded to our SAP accounting system, and if everything isn't just so, SAP throws its toys out of the cot.

Currently the reason why I initially need the datatype as Date is because I've got a loop checking and modifying entries, so I've got several test recordsets in which I look for a date using .FindFirst, and then returns other data associated with that record. Given the amount of time it took me to get this check to work, I'm loathed to change it, but it might be easier to change both dates into strings at the outset and try that way.

Mark_ · Jul 12, 2018

Could you have the date in question in your query twice? Once as a string for export and again as an actual date to be used for your check?

As annoying as it sounds, you may need to have two queries, the first with your date as a date to be used, the second with every thing from the first formatted for export.

isladogs · Jul 12, 2018

OK - sorry
I've just checked and I can't return that peculiar format back to a date

For info, CDate(Format([ActivityDate], ""dd.mm.yyyy"")) is incorrect
Using double " quotes doesn't help at all

Format(DateField,"dd\.mm\.yyyy") returns a string - Access adds the \ automatically
CDate(Format(DateField,"dd\.mm\.yyyy")) returns #Error

Format(CLng(DateField),"dd\.mm\.yyyy") returns a string
CDate(Format(CLng(DateField),"dd\.mm\.yyyy")) returns #Error

BTW you shouldn't use Date as a field name as its a reserved word

You need to find another way such as that suggested by Mark

Format Field while retaining Datatype (1 Viewer)

Spackle

Registered User.

Gasman

Enthusiastic Amateur

Ranman256

Well-known member

Spackle

Registered User.

isladogs

MVP / VIP

Spackle

Registered User.

Mark_

Longboard on the internet

Spackle

Registered User.

Mark_

Longboard on the internet

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread