DLookup

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

There is a field on one of the tables I have to query that
gives me the date, but it is stored as text (24OCT03). I
want to query the text/date field so that it just gives me
a number instead of the format it is currently in. So I
have set up a table with two fields: ID (text, 2) and Mon
(text, 3). My plan is to grab text from the field and
then look up the value in my table and have it give me the
value I want. Anyway, it's not working and any help would
be appreciated, thanks in advance.

DateNum: DLookUp("ID","tbl_Ref","[Mon] = Mid([DATE],3,3)")

Note: [DATE] refers to the name the field I am grabbing
data from.
 
There is a field on one of the tables I have to query that
gives me the date, but it is stored as text (24OCT03). I
want to query the text/date field so that it just gives me
a number instead of the format it is currently in. So I
have set up a table with two fields: ID (text, 2) and Mon
(text, 3). My plan is to grab text from the field and
then look up the value in my table and have it give me the
value I want. Anyway, it's not working and any help would
be appreciated, thanks in advance.

DateNum: DLookUp("ID","tbl_Ref","[Mon] = Mid([DATE],3,3)")
Click to expand...

Three suggestions:

- Date is a reserved word, for the Date() function which gets the
system date. It's risky to use it as a fieldname.

- For a Text field criterion you need quotemark delimiters, and you
should take the Mid() function outside the quotemarks: try

DLookUp("ID","tbl_Ref","[Mon] = '" & Mid([DATE],3,3) & "'")

so the criterion becomes [Mon] = 'OCT' .

- You may want to convert this text date into a real Date/Time
instead: CDate() apparently can't parse 24OCT03 but it works fine on
24-OCT-03, so try

CDate(Format([DATE], "@@-@@@-@@"))
 
John, I went with the CDate conversion, it was much easier
than my way. Thanks for the help.

-----Original Message-----
There is a field on one of the tables I have to query that
gives me the date, but it is stored as text (24OCT03). I
want to query the text/date field so that it just gives me
a number instead of the format it is currently in. So I
have set up a table with two fields: ID (text, 2) and Mon
(text, 3). My plan is to grab text from the field and
then look up the value in my table and have it give me the
value I want. Anyway, it's not working and any help would
be appreciated, thanks in advance.

DateNum: DLookUp("ID","tbl_Ref","[Mon] = Mid
Click to expand...
([DATE],3,3)")

Three suggestions:

- Date is a reserved word, for the Date() function which gets the
system date. It's risky to use it as a fieldname.

- For a Text field criterion you need quotemark delimiters, and you
should take the Mid() function outside the quotemarks: try

DLookUp("ID","tbl_Ref","[Mon] = '" & Mid([DATE],3,3) & "'")

so the criterion becomes [Mon] = 'OCT' .

- You may want to convert this text date into a real Date/Time
instead: CDate() apparently can't parse 24OCT03 but it works fine on
24-OCT-03, so try

CDate(Format([DATE], "@@-@@@-@@"))



.
Click to expand...
Click to expand...
 
Back
Top