I'm 3/4 way through this new site. www.vbcontracts.com and I've got a query on the front page that lets you search jobs in a database. The form has two things to let you search on: Location - there is a drop down list of 14 UK geographical locations. There is also an 'all' location if you are that mobile that you can take a contract anywhere in the country. The SQL statement is currently this, and (thanks to Spooky!!) it works for geographical location search:
SELECT * FROM "vacancy_list Query"
WHERE Job_locator LIKE '::Job_locator::'

However, there is a second search criteria in the same form which is meant to limit the search to jobs posted in the last 1,2,3,4 or 5 days. I've got an automatic date stamp in the DB as to when each job was added. I'm trying to get the date filter working, and I've tried this as suggested by KeithG:

SELECT * FROM "vacancy_list Query"
WHERE Job_locator LIKE '::Job_locator::'
AND (Job_date => Date()-': ays::')

However, I get the following error in FP...

Server error: Unable to retrieve schema information from the query:

SELECT * FROM "vacancy_list Query"

WHERE Job_locator LIKE '1'

AND (Job_date => Date()-'2')

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/vacancies.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Job_locator LIKE '1'

AND (Job_date => Date()-'2')'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

Does anyone know what the way around this is? Is there a way? Am I doomed?

SELECT * FROM "vacancy_list Query"
WHERE Job_locator LIKE '::Job_locator::'
AND (Job_date => DATEADD('d',-2, Now()))