Passing vba variables to an SQL query...

[koba]

I've just started using vba with Access and am still trying to get my head around using SQL within a database I am creating.

Basically, this bit of code works fine until I try to get it to be a bit more flexible. The aim of it is to display a graph showing two axes of results versus a single month of the year - the SQL statement creates a table which is used by a query within the form 'Graph' to show a graph. This bit works and creates the table with the data I require:

Private Sub Command56_Click()
Dim dbs As Database, rst As Recordset
Dim varposition As Variant

DoCmd.SetWarnings (False)

If Combo52 = "-SELECT A MONTH-" Then
Message ("You have not specified a month. Click OK to return to select a month.&quot
Else
DoCmd.RunSQL "SELECT Date, Nitrate, SSVI INTO GData FROM [Daily Water Treatment Plant Readings] WHERE Date Between #01/05/01# And #31/05/01;"
End If

DoCmd.OpenForm "Graph"

End Sub

But when I make a few changes to try and get the date to be user specified through a combobox, the code doesn't seem to work:


Private Sub Command56_Click()
Dim dbs As Database, rst As Recordset
Dim varposition As Variant
Dim d1 As String
Dim d2 As String

If Combo52 = "January" Then
d1 = "#01/01/01#" And d2 = "#31/01/01#"
End If

DoCmd.SetWarnings (False)

If Combo52 = "-SELECT A MONTH-" Then
Message ("You have not specified a month. Click OK to return to select a month.&quot
Else
DoCmd.RunSQL "SELECT Date, Nitrate, SSVI INTO GData FROM [Daily Water Treatment Plant Readings] WHERE Date Between d1 And d2;"
End If

DoCmd.OpenForm "Graph"

End Sub

I know it's probably something very simple, but can anyone point me in the right direction?

Thanks for your time.

 

[atz]

hi,
this might be a red herring, but have you tried setting the dates as a 'YYYYMMDD' format, then passing in as a string?

Regards
;-)

 

[Cagliostro]

Create a function what returns your variable. In the query use the function instead of your variable. John Fill

ivfmd@mail.md

 

[Kubilus1]

Ahh, your sending the variable name, not the data inside the variable to SQL. Try this:

DoCmd.RunSQL "SELECT Date, Nitrate, SSVI INTO GData FROM [Daily Water Treatment Plant Readings] WHERE Date Between " & d1 & " And " & d2 & ";"

Matt

 

[Jduke]

I would like to pass a vba string variable to a SQL statemnet to create a query in Access97 and tried the above suggestion with no sucess. I still seem to pass the variable name and not the variable string value.

I want to send that value to the SQL statement so a user can search a table with wildcards attached to the above string variable.

Ex. would be
SELECT * FROM XTABLE WHERE Issue like "*'Ball*'";

That works fine but when I want to use a variable pulled from a field on a form I can not get the value to pass to SQL. I hope once I can do that then I can add the wildcards for the querying purposes.

Ex. would be

strPartialIssue = [Forms]![Issue Querying]![ISSUESEARCH]

SELECT * FROM XTABLE WHERE Issue LIKE "*'strPartialIssue*'";

I have tried several variations that either kick back syntax errors or just pass the variable name to SQL.

Any help would be greatley appreciated.

Thanks in advance.

 

[tlbroadbent]

Try this.

DoCmd.RunSQL "SELECT * FROM XTABLE WHERE Issue LIKE '*" & strPartialIssue & "*';"



Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Kubilus1]

I'm no expert but aren't you supposed to use a '%' for a wildcard in SQL not a '*'

Matt

 

[camelman]

There are 2 issues here.
1) as one of the other guys who replied to your thread correctly said - you are passing the variable names and not the variables themselves. Your code should look like this

DoCmd.RunSQL "SELECT Date, Nitrate, SSVI INTO GData FROM [Daily Water Treatment Plant Readings] WHERE Date Between " & d1 & " And " & d2

Microsoft access does a funny thing with dates sometimes.

Below I have used a start-date, end-date in the filter for a report. I have dimmed them as type DATE.
I couldn't work out why this filter wasn't working until I wrote this little function to swap the month and the day around (ie: 4/5/2001 becomes 5/4/2001)
This filter works 100% (I didn't use a BETWEEN like you did, but it shouldn't make a difference.
I hope this is of some help.


If (Me![start date] <> &quot;&quot And (Me![end date] <> &quot;&quot Then
S_Date = Month([start date]) & &quot;/&quot; & Day([start date]) & &quot;/&quot; & Year([start date])
E_Date = Month([end date]) & &quot;/&quot; & Day([end date]) & &quot;/&quot; & Year([end date])
If filter_string <> &quot;&quot; Then filter_string = filter_string & &quot; and &quot;
filter_string = filter_string & &quot;([date] >= (#&quot; & S_Date & &quot;#)) and ([date] <= (#&quot; & E_Date & &quot; 11:59:59 PM#))&quot;
End If

DoCmd.OpenReport &quot;My report&quot;, acViewPreview, , filter_string