Searching by Todays Date using SQL statement.

[chillfactor]

Hi all,

I hope someone can help me with this problem I've got. I'll start with the background, I have an access database that we use as a HelpDesk package. We send out daily stats to clients and need to be able to bring up how many new calls have been opened today. I've written a SQL statement that does all that apart from the today bit. At the moment I'm getting all open calls no matter what the date. Here's the code.

SELECT Count(*) AS CallCount FROM calls WHERE Company_ID=7 AND Call_Completed=False AND On_Hold=False AND Call_Date=Date()


As far as I'm aware Date() is a VBA function, but is there a SQL equivelant?

Has anyone got any hints? I'm quite a bit stuck with it and would appreciate a fresh perspective.

Thanks,

Steve B.

 

[Databaseguy]

Try this

SELECT Count(*) AS CallCount FROM calls WHERE Company_ID=7 AND Call_Completed=False AND On_Hold=False AND Call_Date=Format(Now(),"MM/DD/yyyy&quot

Change the mm/dd/yyyy to match your format.

 

[dirkg]

you can use the Date()-function also in SQL. I don't see why your statement doesn't work. I don't think even the format-statement is needed nand you should definitely not choose another format since SQL always uses the format mm/dd/yyyy. Maybe you should check the data-type of your Call_Date - field

 

[chillfactor]

Thanks for the quick response guys, I'll check the SQL code out tomorrow when I'm back at work. As for the format of the call_date field, its set to short date format, is this the right type?

Thanks again,

Steve.

 

[chillfactor]

Ok, I've tried the Format option but that hasn't worked either. I'm still getting all the open calls rather than just todays.

Do I need to set the Input Mask as well as the Format for the Call_Date field in the table as when I am looking through the table contents, Call_Date is being shown as MM/DD/YYYY until I select one of the records. It then changes to the General Date format, ie it shows the Date and Time. Could this be what is stopping it working? If this isn't clear enough then please let me know and I'll try to rephrase it.

Cheers,
Steve.