Cannot set Date Criteria

[Eljefegeneo]

I have a query that has a date field on it. I have a form that selects the date for which I want to have all results >= the date selected by the date picker.


The query without criteria is:

  SELECT tblSalesContactsFinal.ContactID, tblSalesContactsFinal.ClientID, tblSalesContactsFinal.DateOfContact
  FROM tblSalesContactsFinal;

However, when I insert a criteria from an unbound form with a date field named [DateOfContact], i.e. any date after a certain date, I get the error message that the expression is typed incorrectly or is too complex. All I have is an unbound form with a date field that I am referencing.
The criteria is:

  >=Forms![frmEmailBlast]![LastContactDate]

The SQL statement that gives me this error code is

  SELECT tblSalesContactsFinal.ContactID, tblSalesContactsFinal.ClientID, tblSalesContactsFinal.DateOfContact
  FROM tblSalesContactsFinal
  WHERE (((tblSalesContactsFinal.DateOfContact)>=[forms]![frmEmailBlast]![LastContactDate]));

Inserting a date in the query criteria works fine, but I just cannot figure out how to pass the criteria from the form to the query.

 

[isladogs]

Use # delimiters for dates:

SELECT tblSalesContactsFinal.ContactID, tblSalesContactsFinal.ClientID, tblSalesContactsFinal.DateOfContact
  FROM tblSalesContactsFinal
  WHERE (((tblSalesContactsFinal.DateOfContact)>=[COLOR="Red"]#" &[/COLOR] [forms]![frmEmailBlast]![LastContactDate][COLOR="red"] & "#[/COLOR]));

 

[Eljefegeneo]

I get a syntax error in the where statement.

 

[isladogs]

Its quite tricky getting all the " & # right
I'd also make sure dates are formatted in US style

Try this:

SELECT tblSalesContactsFinal.ContactID, tblSalesContactsFinal.ClientID, tblSalesContactsFinal.DateOfContact
  FROM tblSalesContactsFinal
  >="#" & Format([forms]![frmEmailBlast]![LastContactDate],"mm/dd/yyyy") & "#"));

 

[Eljefegeneo]

Still getting the error mesage (the expression is typed incorrectly or is too complex) with:

SELECT tblSalesContactsFinal.ContactID, tblSalesContactsFinal.ClientID, tblSalesContactsFinal.DateOfContact
FROM tblSalesContactsFinal
WHERE (((tblSalesContactsFinal.DateOfContact)>="#" & ([forms]![frmEmailBlast].[LastContactDate]) & "#"));

I tried the following and still get the same error message.

I put an unbound text box on the form [txtDate] and set the after update for [LastContactDate] to:

Private Sub LastContactDate_AfterUpdate()
Me.txtDate = "#" & LastContactDate & "#"

End Sub

Which should be exactly what the criteria is if one would type it in. And then set the criteria as txtDate instead of LastContactDate. Still get the same error message

One more try, I set the txtDate as follows:

Me.txtDate = ">=" & " " & "#" & LastContactDate & "#"

Still get the error message as above using the txtDate as the criteria.

The unbound field [LastContactDate] is set as a short date.

 

[JHB]

Your query in post #1 should be okay.
I would suggest create a new query and use the "Builder" to insert the criteria.
Else post your database with some sample data.

Only a thought, is it a text control or a combo box you get the criteria from?

 

[The_Doc_Man]

Let's start by simplifying... For a single-table query you don't need to qualify the source table. For the single-table case, there is only one possible table anyway and it is the default, so can be omitted from the SELECT clause.

SELECT ContactID, ClientID, DateOfContact
    FROM tblSalesContactsFinal
        WHERE DateOfContact>=[forms]![frmEmailBlast]![LastContactDate];

If you are trying to make this a static (stored) query with a reference to form, try a different reference format.

SELECT ContactID, ClientID, DateOfContact
    FROM tblSalesContactsFinal
        WHERE DateOfContact>=CDate(Forms("frmEmailBlast")!LastContactDate);

 

[isladogs]

Apologies I misread post #1
My code assumed you were setting the form conditions using VBA as your last paragraph mentioned SQL
Yes I know the first line said 'query'.... obviously a short attention span...!

You don't need the #in a query as the query designer handles the delimiters.
However it should still have worked as a query

The fact you are still having issues makes me wonder if txtDate is holding a text field rather than a date.
If so, doc's second suggestion using CDate should fix it

 

[Uncle Gizmo]

Let's start by simplifying... For a single-table query you don't need to qualify the source table.

Further to Doc_Mans good advice, here's a simple way to remove them:-

Replace Superfluous Table name in Single Table SQL Statement



More Tips HERE:- Video Hints & Tips

 

[Eljefegeneo]

Just so you know I have received all the recommendations but have not had a chance to do anything about it yet.

Having an air show today. B-17's, B-24's, B-25's and P-51 Mustang flying overhead. My uncle flew in a B-24 out of Italy, shot down twice, fortunately ended up in Switzerland. Radio man and side gunner.

Will work on it tonight or tomorrow for sure. Thanks.

 

[The_Doc_Man]

I've got a couple of grandsons who would LOVE to be watching that - particularly the P-51 for the younger one and the B-17 for the older one.

 

[Eljefegeneo]

Well, thank you all. I finally got going on this (the planes are still flying overhead) and lo and behold my original SQL is working just fine. Cannot figure out what I am doing any different now than before, but maybe my computer just didn’t like me last week.

I did not do a compact and repair either.

To answer you all in order:

JHB, it turns out you were right. Nothing wrong with my query at all. It was an unbound text box formatted as short date.

Doc Man, your select query (first SQL posted) was fine, but in essence, just what I had. Your second suggested SQL kept giving me an error until I changed it to:

  WHERE DateOfContact>=CDate(Forms!frmEmailBlast!LastContactDate);

Ridders thank you for your time and effort.

Uncle Gimo, thank you for your input. I do find your You Tube postings helpful.

The only thing that is still confusing is why last week it didn’t work and now it does. Could it be my computer which is acting very slow right now – getting old like me. I did notice that when I changed the date on the unbound form "frmEmailBlast" and then went from design view to datasheet view, I got the dreaded error message as stated in my first post. But when I close the query and then open it all is fine.

I guess I will have to monitor this as I continue the development.

Thanks again.

 

[The_Doc_Man]

As to my suggestion, it is (or should be) clear that there are many ways to make things work in Access. Glad you found one that did.