Search breakdown

If dep_date field is a datetime field, then you shouldn't have to break up the search criteria with the date component comparisons.

What exactly is not working?

Hi Aikimark, when I use just the datetime field, when the start of the month comes around, for some reason the month and date swap around and some products do not show (because of the date).

Since you are in a non-American country, you are probably experiencing the mm/dd/yyyy vs. dd/mm/yyyy date formatting difference.

Yes, that is correct

what are the regional settings?

How do you mean? Sorry a little unfamiliar with regional settings

Regional settings is a Windows setting that determines how the operating system treats and displays data; things like your time zone and country.  When you enter dates, the system would expect your date to be in a particular format, based on these settings.

Unfortunately I do not have control over where the website is hosted in the US, but im guessing in US format. On my home server, its in AU format, but same issue.

Where/how does a "website" fit into your configuration?

How many servers are you using?

I have the hosted server in the US (which is live and has the website), and a test server at my house/AU

Is the back-end database MS Access, SQL Server, or some other RDBMS?

MS Access

So for 'yourdatevariable' use Date() ? ie today

sure

Thanks, I have just tried and got this error:

Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: " & Day(Date()) & "]'

Set Prod=oConnUSR.Execute("SELECT * FROM departures WHERE dep_date=> " & DateSerial( " & Year(Date()) & " , " & Month(Date()) & " , " & Day(Date()) & " ) )

Select allOpen in new window

I think there's a missing quote character near the end.  Try this:

& Day(Date()) & " )" )

Select allOpen in new window

I took it out because there was 1 too many, error below:

Microsoft VBScript compilation error '800a0409'
Unterminated string constant
/home.asp, line 576

Set Prod=oConnUSR.Execute("SELECT * FROM departures WHERE dep_date=> " & DateSerial( " & Year(Date()) & " , " & Month(Date()) & " , " & Day(Date()) & " ) " )
-------------------------------------------------------------------------------------------------------------------------------------------------------------^

Select allOpen in new window

Ah.  My code was missing a quote from the beginning of the second line.  Please test this:

Set Prod=oConnUSR.Execute("SELECT * FROM departures WHERE dep_date=> " &  _ 
    "DateSerial(" & Year(yourdatevariable) & ", " & Month(yourdatevariable) & ", " & _
    Day(yourdatevariable) & ")" )

Select allOpen in new window

Ah thanks for that, I have added it in and now have this error:

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'dep_date=> DateSerial(2022, 3, 2)'.
/home.asp, line 576

Select allOpen in new window

Please check the name of the datetime field in your departures table.

Also, try enclosing the name of the field in square brackets:

[dep_date]=>

Select allOpen in new window

Double checked the field name, definitely dep_date. Do you need to know the Format and such?

Same error with brackets

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression '[dep_date]=> DateSerial(2022, 3, 3)'.
/home.asp, line 576

Select allOpen in new window

Please post the statement you are using.

Got it. The comparison operator needs to be reversed.

[dep_date] >=

Select allOpen in new window

Just updated it, this is the error:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/home.asp, line 579

Select allOpen in new window

This is the code:

   Set Prod=oConnUSR.Execute("SELECT * FROM departures WHERE [dep_date]>= " &  _ 
    "DateSerial(" & Year(Date()) & ", " & Month(Date()) & ", " & _
    Day(Date()) & ")" )

Select allOpen in new window

double check the table name and the field name

Just checked again, letter for letter, its all correct

If someone has made a change to a table, you might need to refresh the tables collection

No changes have been made to the table

My current statement works with it:

Set Prod=oConnUSR.Execute("SELECT * FROM departures WHERE dep_date>#"&Date()&"# ORDER BY dep_date")

Select allOpen in new window

If you open the database on your PC and run the (DateSerial) resolved string expression query, what do you see?

Data type mismatch in criteria expression

what is the data type of the dep_date column?

date/time

If you set a breakpoint at the execute statement, copy/paste the query string into the Immediate window (Ctrl+G) and put a question mark character at the very start of the line, before the quote character.

Example:

?"SELECT * FROM departures WHERE [dep_date]>= " &  _ 
    "DateSerial(" & Year(Date()) & ", " & Month(Date()) & ", " & _
    Day(Date()) & ")"

Select allOpen in new window

Then copy/paste the result of that operation (? is actually debug.print) into a query design window (SQL view) and run the query.  Do you get the same error?  If no error, or a different error message, what do you see?

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or ' UPDATE'.

What did you paste (what was the SQL you were trying to run)?

The same SQL statement as you gave me:

?"SELECT * FROM departures WHERE [dep_date]>= " &  _ 
    "DateSerial(" & Year(Date()) & ", " & Month(Date()) & ", " & _
    Day(Date()) & "

Select allOpen in new window

Seems like it didnt like the ?

Did you press the Enter key?
If so, it would resolve that string expression.  The resolved string expression is what you should copy/paste into the query design SQL window.

I cut and pasted what you gave me into the SQL View and then Ran it.

I need you to press the Enter key in the Immediate window after you paste the string and prefix it with a question mark.  Since this may be multiple lines, you may need to press the Enter key multiple times, until you see a new line appear in the Immediate window.  That new line is what you copy/paste into the query SQL view.

Sorry for the confusion.

I pasted it into the SQL view, pressed enter 4 times after the paste, ran the query, same error

I am not really sure I understand what I am doing correct to your instruction

Does your query contain question mark characters?  It shouldn't.

it does at the front, like you gave me to use

I was playing around with how DateSerial should be showing, figured out it should be like this

dep_date>"&("DateSerial("&Year(Date)&","&Month(Date)&","&Day(Date)&")")&"

Select allOpen in new window

For some reason needed brackets around it and now it works fine

First, you copy/paste the quoted string into the Immediate window, prefix the first quote with a question mark and press the Enter key until you see a new line appear below what you pasted.

Second, you copy/paste that new line (should not start with a quote character) from the Immediate window into your SQL view window.

Third, you run the query.

Four, report the results (good, error messages, etc.)

What did you do to "fix" the problem?

I posted how I fixed it 3 posts back