VBA date query - not getting current date 
Author Message
 VBA date query - not getting current date

Hi, I have the following code extract, trying to access a
table and look for only records for the current date:

/===== start of code
Set dbsCurrent = CurrentDb()

xSQL = "SELECT schedule.DATE, schedule.MU_ID, * FROM
schedule LEFT JOIN agent ON schedule.AGENT_ID =
agent.AGENT_ID WHERE (((schedule.DATE)=#" & Date & "#) AND
((schedule.MU_ID)=81)) OR (((schedule.MU_ID)=82)) OR
(((schedule.MU_ID)=83)) OR (((schedule.MU_ID)=602)) OR
(((schedule.MU_ID)=608)) OR (((schedule.MU_ID)=851)) OR
(((schedule.MU_ID)=14)) OR (((schedule.MU_ID)=15)) OR
(((schedule.MU_ID)=16)) OR (((schedule.MU_ID)=17)) OR
(((schedule.MU_ID)=305)) OR (((schedule.MU_ID)=306)) OR
(((schedule.MU_ID)=6));"

Set rstOutput = CurrentDb.OpenRecordset(xSQL)
'rstOutput = dbsCurrent.Execute("GetSchedules")
Set rstOutputSave = dbsCurrent.OpenRecordset("ShiftOutput")
/===end of code

The problem is that the code works and retrieves records,
but for all dates in the database. I have watched the xSQL
variable and the query string shows only the current date.
I can't figure out what I'm doing wrong and would love
some help please?

Many thanks,
Pete



Mon, 26 Sep 2005 09:22:31 GMT  
 VBA date query - not getting current date
Pete,

It seems you are applying the following logic;

select where
(schedule.DATE = DATE AND schedule.MU_ID=81)
or
(schedule.MU_ID=82)
or
(schedule.MU_ID=83)
or
etc...

If I have read the brackets right, wouldn't the date criterea only apply to
schedule.MU_ID=81 ?


Quote:
> Hi, I have the following code extract, trying to access a
> table and look for only records for the current date:

> /===== start of code
> Set dbsCurrent = CurrentDb()

> xSQL = "SELECT schedule.DATE, schedule.MU_ID, * FROM
> schedule LEFT JOIN agent ON schedule.AGENT_ID =
> agent.AGENT_ID WHERE (schedule.DATE=#" & Date & "# AND
> (schedule.MU_ID=81)) OR ((schedule.MU_ID=82)) OR
> (((schedule.MU_ID)=83)) OR (((schedule.MU_ID)=602)) OR
> (((schedule.MU_ID)=608)) OR (((schedule.MU_ID)=851)) OR
> (((schedule.MU_ID)=14)) OR (((schedule.MU_ID)=15)) OR
> (((schedule.MU_ID)=16)) OR (((schedule.MU_ID)=17)) OR
> (((schedule.MU_ID)=305)) OR (((schedule.MU_ID)=306)) OR
> (((schedule.MU_ID)=6));"

> Set rstOutput = CurrentDb.OpenRecordset(xSQL)
> 'rstOutput = dbsCurrent.Execute("GetSchedules")
> Set rstOutputSave = dbsCurrent.OpenRecordset("ShiftOutput")
> /===end of code

> The problem is that the code works and retrieves records,
> but for all dates in the database. I have watched the xSQL
> variable and the query string shows only the current date.
> I can't figure out what I'm doing wrong and would love
> some help please?

> Many thanks,
> Pete



Mon, 26 Sep 2005 12:18:09 GMT  
 VBA date query - not getting current date
This might be a simpler method to write the same SQL:

xSQL = "SELECT schedule.DATE, schedule.MU_ID, * " & _
    "FROM schedule LEFT JOIN agent ON " & _
    "schedule.AGENT_ID = agent.AGENT_ID " & _
    "WHERE schedule.DATE=#" & Date & "# AND " & _
    "schedule.MU_ID IN (81, 82, 83, 602, 608, 851, 14, 5, " &
    "16, 17, 305, 306, 6);"

--
Duane Hookom
MS Access MVP


Quote:
> Pete,

> It seems you are applying the following logic;

> select where
> (schedule.DATE = DATE AND schedule.MU_ID=81)
> or
> (schedule.MU_ID=82)
> or
> (schedule.MU_ID=83)
> or
> etc...

> If I have read the brackets right, wouldn't the date criterea only apply
to
> schedule.MU_ID=81 ?



> > Hi, I have the following code extract, trying to access a
> > table and look for only records for the current date:

> > /===== start of code
> > Set dbsCurrent = CurrentDb()

> > xSQL = "SELECT schedule.DATE, schedule.MU_ID, * FROM
> > schedule LEFT JOIN agent ON schedule.AGENT_ID =
> > agent.AGENT_ID WHERE (schedule.DATE=#" & Date & "# AND
> > (schedule.MU_ID=81)) OR ((schedule.MU_ID=82)) OR
> > (((schedule.MU_ID)=83)) OR (((schedule.MU_ID)=602)) OR
> > (((schedule.MU_ID)=608)) OR (((schedule.MU_ID)=851)) OR
> > (((schedule.MU_ID)=14)) OR (((schedule.MU_ID)=15)) OR
> > (((schedule.MU_ID)=16)) OR (((schedule.MU_ID)=17)) OR
> > (((schedule.MU_ID)=305)) OR (((schedule.MU_ID)=306)) OR
> > (((schedule.MU_ID)=6));"

> > Set rstOutput = CurrentDb.OpenRecordset(xSQL)
> > 'rstOutput = dbsCurrent.Execute("GetSchedules")
> > Set rstOutputSave = dbsCurrent.OpenRecordset("ShiftOutput")
> > /===end of code

> > The problem is that the code works and retrieves records,
> > but for all dates in the database. I have watched the xSQL
> > variable and the query string shows only the current date.
> > I can't figure out what I'm doing wrong and would love
> > some help please?

> > Many thanks,
> > Pete



Mon, 26 Sep 2005 12:33:04 GMT  
 VBA date query - not getting current date
Thanks to you both for your prompt response. Guess who
feels silly???

Damn those brackets!!

Seriously, a big help in both identifying the error and
suggesting a better query construct.

Cheers,
Pete

Quote:
>-----Original Message-----
>Pete,

>It seems you are applying the following logic;

>select where
>(schedule.DATE = DATE AND schedule.MU_ID=81)
>or
>(schedule.MU_ID=82)
>or
>(schedule.MU_ID=83)
>or
>etc...

>If I have read the brackets right, wouldn't the date

criterea only apply to
Quote:
>schedule.MU_ID=81 ?



>> Hi, I have the following code extract, trying to access
a
>> table and look for only records for the current date:

>> /===== start of code
>> Set dbsCurrent = CurrentDb()

>> xSQL = "SELECT schedule.DATE, schedule.MU_ID, * FROM
>> schedule LEFT JOIN agent ON schedule.AGENT_ID =
>> agent.AGENT_ID WHERE (schedule.DATE=#" & Date & "# AND
>> (schedule.MU_ID=81)) OR ((schedule.MU_ID=82)) OR
>> (((schedule.MU_ID)=83)) OR (((schedule.MU_ID)=602)) OR
>> (((schedule.MU_ID)=608)) OR (((schedule.MU_ID)=851)) OR
>> (((schedule.MU_ID)=14)) OR (((schedule.MU_ID)=15)) OR
>> (((schedule.MU_ID)=16)) OR (((schedule.MU_ID)=17)) OR
>> (((schedule.MU_ID)=305)) OR (((schedule.MU_ID)=306)) OR
>> (((schedule.MU_ID)=6));"

>> Set rstOutput = CurrentDb.OpenRecordset(xSQL)
>> 'rstOutput = dbsCurrent.Execute("GetSchedules")
>> Set rstOutputSave = dbsCurrent.OpenRecordset
("ShiftOutput")
>> /===end of code

>> The problem is that the code works and retrieves
records,
>> but for all dates in the database. I have watched the
xSQL
>> variable and the query string shows only the current
date.
>> I can't figure out what I'm doing wrong and would love
>> some help please?

>> Many thanks,
>> Pete

>.



Mon, 26 Sep 2005 13:38:24 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Date Difference to Return the most Current Date

2. Changing a Calculate field (Date) depending on Current date

3. Any code for current date to Julian date samples

4. date range to current date at specific time syntax question

5. Q:get current date and then create date range

6. Getting the current date when the ChangeDate event fires

7. getting the current date from the server ?

8. SQL FoxPro ODBC Table Query using Current Date

9. MS-ACCESS query date problem + date formatting in datagrid

10. CurrentDate, etc. not showing current date

11. VBA: Getting a error setting a Date/Time = to another D/T field

12. Getting Information on a DLL'd date or version using VBA

 

 
Powered by phpBB® Forum Software