Parameter in a sql string for form

  • Thread starter Thread starter c8tz
  • Start date Start date
C

c8tz

Hi,

I have a form such that when the user enters the date in the date
field it should show the top 3 dates equal to and less than the date
specified.
I am able to do this in a query but when I use that query as an SQL
string in my coding - it doesn't work -


....code..


sqlstring: select top3 date from frondmarkingtable where date <= "
&txtdate&" ;


this works fine as a query but in the code - nothing happens and no
dates are returned.


Please help.
 
Concatentate the date into the string.
Use # as the delimiter:

Dim sqlstring As String
If IsDate(Me.txtDate) Then 'If it's not null
sqlstring = "select top3 date from frondmarkingtable " & _
"where (frondmarkingtable.[date] <= " & _
Format(Me.txtdate, "\#mm\/dd\/yyyy\#") & ");"
'Debug.Print sqlstring
End If

Hopefully your field isn't really called "date", as that is a reserved word:
http://allenbrowne.com/AppIssueBadWord.html
 
c8tz said:
I have a form such that when the user enters the date in the date
field it should show the top 3 dates equal to and less than the date
specified.
I am able to do this in a query but when I use that query as an SQL
string in my coding - it doesn't work -

...code..

sqlstring: select top3 date from frondmarkingtable where date <= "
&txtdate&" ;

this works fine as a query but in the code - nothing happens and no
dates are returned.
Click to expand...


But that's not code.

If you are talking about it in a VBA procedure you have to
put the SQL statement in quotes and do something to run the
query. If you want to open a form to display the query's
records, then use the SQL statement (without quotes in the
form;s RecordSource property.

Your syntax looks more like you are trying to use it as a
subquery in a calculated field in some other query, but a
calculated field subquery must return a single value so it
can't work that way.
 
Back
Top