Dates as Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.

Van T. Dinh said:
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)




judyblue said:
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
Click to expand...
Click to expand...
 
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)
 
Back
Top