trouble with SQL syntax

I'm having some trouble getting a search in an access database to work
the way I want.I have a table with several fields and I need to use
three of them for the search filter.
the three fields are Model,StartYr and EndYr.I would like to search a
Model between StartYr and EndYr.On the form I have a combo named
cboModel and a combo array named
combo1(0),thats StartYr and combo1(1) thats EndYr.When I select a year
on the combo1 an integer variable Sdate or Edate is filled in.

the below codes works exactly the way I want, but there are no dates
Private Sub cmdSearch_Click()
Dim mySQL As String

mySQL = "SELECT * From csparts WHERE Model LIKE " & """*" &
cboModel.Text & "*"""

Data1.RecordSource = mySQL
Data1.Refresh
Data1.Recordset.MoveLast
Label5.Caption = "Number of Records
Retrieved : " & Data1.Recordset.RecordCount
Data1.Recordset.MoveFirst
Data1.Refresh

when I change the mySQL to include a date I get errors or else no
results at all

mySQL = "SELECT * From csparts WHERE Model LIKE " & """*" &
cboModel.Text & "*""" AND StartYr >= (SDate) AND EndYr <= (EDate)

the database contents was supplied to me as an excel file and many of
the EndYr entries are "later" so as a result the StartYr and EndYr
fields are defined as String
in the combo1(1).click event if the text is "later" then Edate
becomes format(now,"YYY")
the error I get most is variable not defined for the StartYr .
If anybody point out what I'm doing wrong if would be much
appreciated
Thanks
SteveC

.

Relevant Pages

  • Re: trouble with SQL syntax
    ... three of them for the search filter. ... Model between StartYr and EndYr.On the form I have a combo named ... Dim mySQL As String ...
    (microsoft.public.vb.general.discussion)
  • Access conversion to SQL
    ... I have an application that currently connects to an Access database, ... customers are asking for a MySql and MsSql server back end database. ... MySql handles True and False values through the bit field without any ... It also gives me the problem of setting controls true/false value by ...
    (microsoft.public.access.conversion)
  • Interesting ODBC problem
    ... I have just converted a guestbook to use an Access database rather than ... MySQL via odbc and have come across an unusual problem. ...
    (comp.lang.php)
  • Re: Pocket Access - Sync to Linked Table?
    ... syncronizes a Pocket Access database with my Access ... I can then maintain my DB within Access and syncronize the PDA ... Recently I've re-created my tables in MySQL, set up ODBC connections to them ... Pocket Access database through ActiveSync anymore because the linked tables ...
    (microsoft.public.pocketpc.activesync)
  • Problems accessing a MySQL database using an Access linked table
    ... between MySQL and another database, I've created an Access database ... application - classic ASP works fine but .NET gives me... ... If you are not the intended recipient please notify the sender that you have ...
    (microsoft.public.dotnet.framework.aspnet)