SQL Question

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have the following SQL Statement in a VBA Function.
When I try to run the function it tells me there is a
syntax error.

SELECT DISTINCT Activity.SlsRepID, Activity.Date,
Activity.Time FROM Activity WHERE (((Activity.SlsRepID)="
& [Forms]![Schedule Data Entry]![SalesRep] & ") AND
((Activity.Date)=#[Forms]![Schedule Data Entry]![cboDate]
#) AND ((Activity.Time)='" & [Forms]![Schedule Data
Entry]![Time] & "'));

This query works in the query builder and will accomplish
what I want, but won't run in VBA. The problem is in the
Where clause and is associated with the date.

Any help you can provide will be greatly appreciated!

Kevin
 
I have the following SQL Statement in a VBA Function.
When I try to run the function it tells me there is a
syntax error.

SELECT DISTINCT Activity.SlsRepID, Activity.Date,
Activity.Time FROM Activity WHERE (((Activity.SlsRepID)="
& [Forms]![Schedule Data Entry]![SalesRep] & ") AND
((Activity.Date)=#[Forms]![Schedule Data Entry]![cboDate]
#) AND ((Activity.Time)='" & [Forms]![Schedule Data
Entry]![Time] & "'));

This query works in the query builder and will accomplish
what I want, but won't run in VBA. The problem is in the
Where clause and is associated with the date.
Click to expand...

Please post the actual VBA context of this statement. If you're trying
to include the SQL statement just as inline VBA code it certainly will
NOT work - VBA is one language, SQL is a different one, and the VBA
compiler doesn't understand SQL at all except as a string variable to
be passed to the query engine.
 
Hi Kevin

I assume you are storing time in a longdate field in SQL.
Depending how you write the item out (Recordset or SQL Insert query) it will
write a phantom date of 01/01/1901 or 30/12/1899 as a "companion date" to
the time.

I found converting the time to a string and then using the TimeValue
function worked for me, but it was a real nightmare.

Here is a snip

WHERE tblContact.AssignedTime Like TimeValue('" & sCheckTime & "')

Put SQL Time into google search and see the 10,000 links you get - you're
not on your own with this problem.

Cheers

Ian B
 
Ian,

The time used is AM, PM and All Day. I have no problems
with this one. It is the date I am having trouble with.

John,

I realize I can not just insert the SQL. I posted only the
SQL string. The code is something like the following:

dim db as database
dim rst as recordset
dim sqlStr1 as string

set db=currentdb

sqlStr1 = "SELECT DISTINCT Activity.SlsRepID,
Activity.Date, Activity.Time FROM Activity WHERE
(((Activity.SlsRepID)=" & [Forms]![Schedule Data Entry]!
[SalesRep] & ") AND ((Activity.Date)=#[Forms]![Schedule
Data Entry]![cboDate]#) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));"

set rst=db.openrecordset(sqlStr1,dbopensnapshot)

etc.

The problem I am having is with the date. I get an error
message indicating a syntax error and the problem appears
to be with how I have the date, coming from an input form,
referenced in the where clause.

I posted only the sql statement because that is what I am
having trouble with.

Thanks to both of you for taking the time to help me!

Kevin
Kevin
 
Ian,

The time used is AM, PM and All Day. I have no problems
with this one. It is the date I am having trouble with.

John,

I realize I can not just insert the SQL. I posted only the
SQL string. The code is something like the following:
Click to expand...

Sorry! That wasn't clear from the post.
dim db as database
dim rst as recordset
dim sqlStr1 as string

set db=currentdb

sqlStr1 = "SELECT DISTINCT Activity.SlsRepID,
Activity.Date, Activity.Time FROM Activity WHERE
(((Activity.SlsRepID)=" & [Forms]![Schedule Data Entry]!
[SalesRep] & ") AND ((Activity.Date)=#[Forms]![Schedule
Data Entry]![cboDate]#) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));"

set rst=db.openrecordset(sqlStr1,dbopensnapshot)

etc.

The problem I am having is with the date. I get an error
message indicating a syntax error and the problem appears
to be with how I have the date, coming from an input form,
referenced in the where clause.
Click to expand...

One VERY possible problem is that Date and Time are reserved words;
you should at the very least always enclose the field names in square
brackets. Note that Date/Time fields can include both portions; I'd be
inclined to use a field ActivityTime in your table which contains both
the date and time, it'll make searching easier.

The problem with the SQL appears to be that you're embedding the forms
reference in the string. Try concatenating its value instead:

sqlStr1 = "SELECT DISTINCT Activity.SlsRepID,
Activity.[Date], Activity.[Time] FROM Activity WHERE
(((Activity.SlsRepID)=" & [Forms]![Schedule Data Entry]!
[SalesRep] & ") AND ((Activity.Date)=#" & [Forms]![Schedule
Data Entry]![cboDate] & "#) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));"

Is this an Access Date/Time field, or some other type of field? Is
Time a Text field as the quotemarks indicate? And what is the bound
column of cboDate?
 
Are you in using mm/dd/yyyy date or dd/mm/yyyy date.
SQL statements will always try to use mm/dd/yyyy unless the date is
obviously dd/mm/yyyy (23/10/2003)

Perhpas try a reformat of [Forms]![Schedule Data Entry]![cboDate] to be a
Medium date which is unambiguous if you are in a dd/mm/yyyy part of world.


Ian B
 
Back
Top