Format Date Function

[AmyLynnHill]

Does anyone know of a function that will change the date format from yyyymmdd to mmddyyyy? I have a linked table to a data repository, I'm using a date field in a query and would like to use the dateadd function to return a certain date range from 7 days prior. I can't get it to run, I thought this might be due to the date format.

here is what i have in the date field as the criteria: <DateAdd("d",-7,Now())
the date field is formated to yyyymmdd
Any suggestions?

 

[RuralGuy]

Is the date field a string or a number?

 

[macca the hacke]

Can you not use a combination of left/right/mid string functions to turn the date round the other way. You can then use cdate round this to force it to be a date again.

 

[neileg]

It depends what type of databse is used as the repository as to how the date is held. If all you are getting is an 8 character string, then you will need to chop this up and then convert to a date.

CDate(Mid([MyDate],5,2)&"/"&Right([MyDate,2)&"/"&Left([MyDate],4))

should do the trick.

 

[AmyLynnHill]

Its a text field

 

[RuralGuy]

Neil's suggestion should work.

 

[AmyLynnHill]

I get a data type mismatch error.
I put the CDate function in the field expression and the DateADD function in the criteria?
Do I need to put a format function before the CDate function?

 

[RuralGuy]

How about posting the SQL for the query you have.

 

[AmyLynnHill]

SELECT CDate(Mid([issue_date],5,2) & "/" & Right([issue_date],2) & "/" & Left([issue_date],4)) AS Issue,
FROM dbo_polc_t
WHERE (((CDate(Mid([issue_date],5,2) & "/" & Right([issue_date],2) & "/" & Left([issue_date],4)))<DateAdd("d",-7,Now())) AND ((dbo_polc_t.zip) Like "452*")) OR (((CDate(Mid([issue_date],5,2) & "/" & Right([issue_date],2) & "/" & Left([issue_date],4)))<DateAdd("d",-7,Now())));

 

[Brianwarnock]

The old brain cells have gone a bit fuzzy in retirement but I seem to remember that there are problems with comparisons and cdate created dates, use Dateserial, also use Date() instead of Now() as that returns a date and time.

Brian

 

[The_Doc_Man]

The idea is sound but there are a couple of issues to consider.

First, split the parts into day, month-number, and year. You've done that.

Second, rearrange the parts into the order required for a date field based on the default setting for Access. If it is mm/dd/yyyy then re-order it that way.

Third, treat this string as a date, but that can only happen by including the syntax for a date. You are inserting the "/" characters so you are aware of that requirement. Like Brian, my old grey cells are mostly dormant, but isn't there also a requirement for the number-sign (#) before and after a text date? Or is that only in constant expressions in VBA? I forget sometimes...

Fourth, as messy as this is getting, I think you would do better to add a new field to this table that is of date format to begin with (not text). Update the field according to the string mucking that you are doing.

Then you can do your comparisons directly.

 

[Brianwarnock]

Hi
It's gone cold out so have come in from the garden and decided to check out what I had said, and if you use dateserial the result is a date formatted according to your default

DateSerial(Right([tdate],4),Mid([tdate],3,2),Left([tdate],2))

returns the good old British format dd/mm/yyyy from a text string ddmmyyyy though dateserial does not give a damn about the original layout.
This then does not return an error when the test < DateAdd("d",-7,Date()) is performed.

Brian