Using the "Format" feature in an Access query.

[mbenton]

Hello, I have a table that I will need to run a query on. The field of interest is not a date field yet has the following format: "YYYYMMDD", it reads the date as a number. The name of the field is called, "PlantTC_TO. In my query design view I have the table and have selected the field. In my criteria section I have: => Format(now(),"YYYYMMDD"). I'm trying to select all values greater than or equal to today's date. I'm not sure what I'm doing incorrectly but I've spent much time trying to get this query correct. The error says, "The expression you entered contains invalid syntax." Any suggestions? Thank you.

[orange]

Why do you have a field that is valued with Now() that is not a date?
What was the purpose of creating a string and trying to do some arithmetic/logic on it?

If you want today's date, Access provides the Date() function.

What exactly are you trying to do --in very plain English?

The format "feature" results in a string.

[CJ_London]

may be sloppy posting but

I have: =>

should be

I have: >=

[mbenton]

I know it's odd...but unfortunately it's the way our company does it. I can't change it...must work with the OBDC source tables. the tables are formatted that way. However, I am needing to extract the date value from this table. This is a time control table and I need to retrieve all dates equal to or greater than today's date. I'm developing a workflow with several tasks and I need the future dates to verify if a task has been completed.

[mbenton]

I meant to say, "ODBC" table.

[orange]

Show us a bit of the code you are working with.

If you get data from an external source that has date and time data stored in a string/text, you could use intrinsic functions to convert those data to Access date/time datatype and possibly take advantage of other functions within Access.

What is the database you are using with ODBC?

If you get data as "YYYYMMDD", you can convert this to an Access date/time variable using something along these lines.

Consider YourField ="YYYYMMDD"
Dim DateYuNeed as date
Dim mYear as Integer
Dim mMonth as Integer
Dim mDay as Integer
mYear = CInt(Left(Yourfield,4))
mMonth = CInt(Mid(Yourfield,5,2))
mDay =CInt(Right(yourfield,2))
DateYuNeed = DateSerial(mYear,mMonth mDay)

In Access, Date is a built in function that returns Today's Date (no time element).

Your question

I'm trying to select all values greater than or equal to today's date.

If DateYuNeed - Date >= 0 then
   'this is where you do whatever you want to do
else
   'these records are less than today's date
end if

I hope this is helpful.

[mbenton]

Thanks, I'll let you know how this turns out when I insert the code. Thanks for your help.

[mbenton]

Thanks all for your help. In an effort to simplify things I decided to work with the "Format" feature rather than the Dim coding. It was correct that I was entering the Format feature incorrectly in my query...it should be >=Format(Now(),"YYYYMMDD"). Problem solved, thanks.