count records in recordset

[coyote69]

I get a syntax error what am i missing

table is contactmanager
field to count is apptdates


Private Sub apptcheck2()
Dim db As Database
Dim rs As Recordset

Dim sqlstring As String

Set db =OpenDatabase("c:\data\op\pos405\contact2\contactmanager.mdb&quot

searchfor = InputBox("Enter appt date&quot



sqlstring = "Select contactmanager.apptdate, count(contactmanager.apptdate)As
NoOfOrders from contactmanager and having ((contactmanager.apptdate) = #" & searchfor & "#"

Set rs = db.OpenRecordset(sqlstring)

picResults.Print rs.Fields(" noOfdates &quot

End Sub

 

[link9]

try axing the AND from your SQL and see if that clears it up. I would also replace HAVING with WHERE. I think that's what you're after.

so:

sqlstring = "SELECT contactmanager.apptdate, count(contactmanager.apptdate) AS NoOfOrders FROM contactmanager WHERE (contactmanager.apptdate) = #" & searchfor & "#"



paul

 

[coyote69]

I now get an error that I am trying to execute a query that does not include the specified expression apptdate as part of an aggregrate expression

 

[egapogi]

Try this..


sqlstring = "Select contactmanager.apptdate, count(contactmanager.apptdate) As NoOfOrders from contactmanager and having ((contactmanager.apptdate) = #" & searchfor & "#)"

* I just inswereted a space bet. "apptdate)" and "As",
Then close bracket after "#".

my 2 cents.

 

[coyote69]

Still the same problem. Syntax problems give me a headache

 

[abdullauthan]

Try this

sqlstring = "SELECT contactmanager.apptdate, count(contactmanager.apptdate) AS NoOfOrders FROM contactmanager WHERE contactmanager.apptdate = #" & searchfor & "# Group By contactmanager.appdate"

 

[coyote69]

Still getting the same message.

 

[Nagrom]

In case you just copied and pasted the code, that "group by" clause should have the field name changed from "appdate" to "apptdate" - there's a typo in there.

 

[coyote69]

If only it was that easy. that did not help it any I still get a syntax error. Thanks for the good observation though.

 

[Nagrom]

Are you sure that the data in the "searchfor" variable is in a valid date format? I tested the SQL directly in Access and it works fine, but I did hard code the date (obviously, since I was directly in an Access query window). I used #8/5/2001# for the date.

 

[coyote69]

I tried that also and now i am getting a type mismatch error

 

[egapogi]

everybody here have there own imagination of what really is happening to your SQL statement..

Maybe it would help if you can send me the table with the data... just the "contactmanager" table in one database, and i'll figure it out...

ega.

ega4htm@yahoo.com

 

[egapogi]

I tried this with youre table and it worked...

"SELECT Format([contactmanager].[ApptDate],"mm/dd/yy&quot AS Expr1, Count(contactmanager.ApptDate) AS NoOfOrders FROM contactmanager
GROUP BY Format([contactmanager].[ApptDate],"mm/dd/yy&quot
HAVING (((Format([contactmanager].[ApptDate],"mm/dd/yy&quot)=#" searchfor & "#))"


- searchfor is assumed to be formated as "mm/dd/yy".
- you need to use the format function because your AppDate Field contains trailing time.

If you equate #12/31/02 10:00:00 AM# with #12/31/02", it will be always false....

good luck!

..you mentioned that you need help regarding combobox, please start a new thread.

cheers,
ega

cheers,

 

[coyote69]

Thank for the help!!