SQL error
- From: "Jim Y" <j.s.yablonsky@xxxxxxxxxxxxxx>
- Date: Tue, 14 Feb 2006 02:08:05 GMT
VB6 and DAO 3.6
I am attempting to permit the user to select 1 of 3 options in the display of database records.
OptSchedule(0) is titled: Schedule to end of year
OptSchedule(1) is titled: Next years schedule
OptSchedule(2) is titled: All records '(this works)
The SQL statement worked before I included the sSchedule variable also.
I get a syntax errors (missing operator) in my WHERE phrase and I cannot find what is wrong.
In the first option, I am attempting to display all records between the present date and the end of
the year - it does not work.
In the second option, I am attempting to display only those records for the next year - 2007 and
beyond - that does not work either.
I always have trouble with dates. What am I doing wrong this time?
Thank you,
Jim Y
Dim sSchedule As String
On Error GoTo terminate
'Setup SQL search schedule statement
If FrmSearch.OptSchedule(0).Value = True Then
sSchedule = "EventDate => #" & Date & "# AND EventDate =< #12/31/" & Format$(Date, "yyyy") &
"# AND "
ElseIf FrmSearch.OptSchedule(1).Value = True Then
sSchedule = "EventDate => #1/1/" & Format$(DateAdd("yyyy", 1, Date), "yyyy") & "# AND "
Else
sSchedule = ""
End If
strSQL = "SELECT LastName, FirstName, ClientID, "
<Snip>
strSQL = strSQL & "Address1, City "
strSQL = strSQL & "FROM tblClient "
strSQL = strSQL & "WHERE " & sSchedule & " LastName LIKE '" & TxtLastName.Text & "*" & "'"
strSQL = strSQL & " ORDER BY LastName, FirstName, "
strSQL = strSQL & "EventDate, PickupTime1, "
strSQL = strSQL & "ClientID"
Set rstClients = FrmClientData.gdbLimo.OpenRecordset(strSQL)
first option result:
SQL = SELECT LastName, FirstName, ClientID, EventDate, PickupTime1, EventType, Vehicles,
NumberOfPassengers, PickUpLoc1, HourlyRate, Hours, AddChg, Deposit, BalanceType, PmtAmt0, PmtAmt1,
PmtAmt2, PmtAmt3, PmtAmt4, PmtAmt5, RateType, Xcharge, PUChour, Address1, City FROM tblClient WHERE
EventDate => #2/13/2006# AND EventDate =< #12/31/2006# AND LastName LIKE '*' ORDER BY LastName,
FirstName, EventDate, PickupTime1, ClientID
second option result:
SQL = SELECT LastName, FirstName, ClientID, EventDate, PickupTime1, EventType, Vehicles,
NumberOfPassengers, PickUpLoc1, HourlyRate, Hours, AddChg, Deposit, BalanceType, PmtAmt0, PmtAmt1,
PmtAmt2, PmtAmt3, PmtAmt4, PmtAmt5, RateType, Xcharge, PUChour, Address1, City FROM tblClient WHERE
EventDate => #1/1/2007# AND LastName LIKE '*' ORDER BY LastName, FirstName, EventDate, PickupTime1,
ClientID
\tThird option result that works and displays all the records
SQL = SELECT LastName, FirstName, ClientID, EventDate, PickupTime1, EventType, Vehicles,
NumberOfPassengers, PickUpLoc1, HourlyRate, Hours, AddChg, Deposit, BalanceType, PmtAmt0, PmtAmt1,
PmtAmt2, PmtAmt3, PmtAmt4, PmtAmt5, RateType, Xcharge, PUChour, Address1, City FROM tblClient WHERE
LastName LIKE '*' ORDER BY LastName, FirstName, EventDate, PickupTime1, ClientID
.
- Follow-Ups:
- Re: SQL error
- From: Steve Gerrard
- Re: SQL error
- Prev by Date: Need to print to specific printers
- Next by Date: Re: how do you terminate an application?
- Previous by thread: Need to print to specific printers
- Next by thread: Re: SQL error
- Index(es):
Relevant Pages
|
