Dates as Text

[Guest]

Hi,

I have a linked table from another program that I cannot change the format
on. They have dates as text and I'm trying to run a query that will return
DateDiff <0 and DateDiff >3. Here is what I've done so far.

Expr1: Format(DateValue([PDATE]),"mm/dd/yyyy")
Expr2: Format(DateValue([DDATE]),"mm/dd/yyyy")

This returns the dates in proper format. My question is, how would I
formulate this in the query to show null values as well as the two mentioned
above?

Hope this makes sense.

Thanks in advance,
Judy

 

[Van T. Dinh]

Are you aware that the return of Format() is a Variant of String type and
not DateTime type?

I am not sure which 2 values you want to take the difference from. Do you
mean the 2 expressions you posted?

Please post some sample Date Text from the linked Table.

 

[Guest]

Van,

Thanks for the reply. Yes, I need the difference from the two expressions
that I posted. And no, I didn't realize that it was not converting it to
DATE format.
Then linked table looks like a date field ex.( 01/01/04), but is a text field.

Are you aware that the return of Format() is a Variant of String type and
not DateTime type?

I am not sure which 2 values you want to take the difference from. Do you
mean the 2 expressions you posted?

Please post some sample Date Text from the linked Table.

--
HTH
Van T. Dinh
MVP (Access)

Hi,

I have a linked table from another program that I cannot change the format
on. They have dates as text and I'm trying to run a query that will return
DateDiff <0 and DateDiff >3. Here is what I've done so far.

Expr1: Format(DateValue([PDATE]),"mm/dd/yyyy")
Expr2: Format(DateValue([DDATE]),"mm/dd/yyyy")

This returns the dates in proper format. My question is, how would I
formulate this in the query to show null values as well as the two mentioned
above?

Hope this makes sense.

Thanks in advance,
Judy

 

[Van T. Dinh]

Assumptions:
1. The Date Text is in correct format according to your
Regional Settings.
2. [PDATE] is earlier than [DDATE]

Then you can use a Calculated Field in your Query:

Diff: DateDiff("d", CDate([PDATE]), CDate([DDATE]))

to work out the difference. You can then set the criteria
on this Field / Column.

Note: it is likely that

Diff: DateDiff("d", [PDATE], [DDATE])

works also since Access/JET does lots of automatic type-
casting for you. However, I tend not to rely on automatic
type-casting.

BTW, I am not sure from your example whether your date
format is mm/dd/yy or dd/mm/yy.

HTH
Van T. Dinh
MVP (Access)