SQL Count thing

[spasticus]

hi again,

i need help... why doesn't this work?

if the SQL (following) can't work, is there another way of trying to count certain records in a table and store the value in a variable?

strSomeString = DoCmd.RunSQL ("SELECT COUNT(*) from tbl_booking where Staff_ID=""" & [Staff_ID] & """")


thanks.

 

[pbaldy]

RunSQL doesn't work with SELECT queries, only action queries, and in any case you couldn't use it like that. Try a DCount with the same criteria.

 

[tehNellie]

I don't believe you can DoCmd.RunSQL in Access on a non action query.

A rather longwinded way is to open the result of the Count query in a recordset and then pass into a variable.

Dcount() or Dlookup() might be a bit more straightforward.

 

[spasticus]

thanks for the relies.

i used dcount() to try and find if any staff members have already been booked at the time and date of the new record.

it didn't work obviously, thats why im back. i think i need to clear some stuff up before i continue..

can you have multiple criteria? eg (in the criteria part) [staff_id]=1 AND .... AND

second question... what exactly is the first part for? i mean DCOUNT("this bit"...
i changed it to a number of fields in the table and it didn't make a difference.
if i've got multiple criteria do i need to have each field im searching in in here? eg DCOUNT("[staff_ID], [Date].... etc or something

you can probably tell i dont know much about anything so i realy appreciate your help. especially as this project has to be done by thursday and i still havent done some of the core features.

anyway.. thanks.

 

[pbaldy]

Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

Typically for DCount just put "*" in the first argument.

 

[spasticus]

thanks.

turns out i was doing it right.. so, why doesn't this work


strbusy = DCount("*", "tbl_booking", "[Staff_ID]= " & [Staff_ID] & " AND [Booking_Date]=#" & [Booking_Date] & "#")

without the date part i get 5, and there are 5 records with the staff id i entered.
with the date part it tells me there's 0, and there's not there's 4.

im completely confused now.. please help me

thanks.. again

 

[pbaldy]

One thought would be if the date field included a time element. Can you post a sample db?

 

[spasticus]

there's no time element to it. in the table the field is set to short date with a 00/00/0000;0;_ input mask (which is on the form as well).

i doubt it would be helpful giving you the database its really confusing and i dont have the time to make a sample one with the problem.

any other ideas what it might be?

.. ill post the entire database if you wanted/think it might help

 

[pbaldy]

If you're saying there should be 4 records that meet both criteria, then I don't see the problem. The DCount looks correct.

 

[gemma-the-husky]

may be you are getting a regional date error with booking_date

try

format(booking_date,"long date"), rather than just booking_date

 

[spasticus]

thanks gemma-the-husky that got it working.