Date Count errors (1 Viewer)

[Chrisopia]

I am trying to add up a list of dates that match a criteria... a search box result.

I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)

But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.

I am sticking with the SQL version (as I've heard it's the most stable)... but I seriously have no idea what I'm doing wrong!?

Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant

DateSearch = [Forms]![EnterDate]![NumberBox]
MyDateAdd = DateAdd("d", 1, DateSearch)

MyDate = "SELECT * FROM tblInvoiceQuote WHERE [Date Paid] >= #" & DateSearch & "# And <#" & MyDateAdd & "#; "
MsgBox MyDate

varReturnValue = CurrentDb.OpenRecordset(MyDate)(0)

'ResultCount = DCount("[InvoiceNumber]", )
MsgBox ResultCount

I've used the >= And < option as it solves an issue with Date Time.

I can't seem to figure out what operator is missing!? :banghead:

 

[pr2-eugin]

Chrisopia, Your WHERE part is a bit off.. It should be..

MyDate = "SELECT * FROM tblInvoiceQuote " & _
        "WHERE [Date Paid] >= #" & DateSearch & "# And [COLOR=Red][B][Date Paid][/B][/COLOR] <#" & MyDateAdd & "#; "

 

[Chrisopia]

Thanks Eugin,

That solved my missing operator conundrum, but it's telling me there are 0 rows?

ID Date Paid
1 21/01/2013 12:18:17
2 21/01/2013 12:20:20
3 21/01/2013 13:05:41
4 21/01/2013 15:24:56
5 23/01/2013 09:36:25
6 23/01/2013 10:25:02
7 23/01/2013 14:32:31
8 29/01/2013 15:14:12

If I search 21/01/2013 (the search box uses a date picker) - it should respond with '4'... but it's replying with '0'

I thought it was a date/time issue, which made sense at the time (there are no entries for 21/01/2013 00:00:00)

 

[pr2-eugin]

So see if this would work..

MyDate = "SELECT * FROM tblInvoiceQuote " & _
         "WHERE [URL="http://www.techonthenet.com/access/functions/date/datevalue.php"]DateValue[/URL]([Date Paid]) >= #" & DateSearch & "# And DateValue([Date Paid]) <#" & MyDateAdd & "#;"

Or a better one.. what happens if you try BETWEEN operator?

MyDate = "SELECT * FROM tblInvoiceQuote " & _
         "WHERE [Date Paid] BETWEEN #" & DateSearch & "# And #" & MyDateAdd & "#;"

 

[Chrisopia]

Ah - apologies... I realise I never declared 'ResultCount' hence the zero... one moment, let me try something...

 

[Brianwarnock]

So see if this would work..

MyDate = "SELECT * FROM tblInvoiceQuote " & _
         "WHERE [URL="http://www.techonthenet.com/access/functions/date/datevalue.php"]DateValue[/URL]([Date Paid]) >= #" & DateSearch & "# And DateValue([Date Paid]) <#" & MyDateAdd & "#;"

Or a better one.. what happens if you try BETWEEN operator?

MyDate = "SELECT * FROM tblInvoiceQuote " & _
         "WHERE [Date Paid] BETWEEN #" & DateSearch & "# And #" & MyDateAdd & "#;"

Both examples here are wrong for different reasons, remember Date Paid has a time element.
Datevalue removes the time so the < now needs to be <=
On the second example you do not use Datevalue so the time comes into play but there is the potential for a time of 00:00:00 on the day after that required to be selected.
In his example 22/01/2013 00:00:00

Brian