Solved: Variables in a Query

[Marcster]

Hello people http://vbaexpress.com/forum/images/smilies/039.gif .

I want to be able to change (which is in the criteria field of a query):
Between [forms]![Form1]![txtTheDateFrom] And [forms]![Form1]![txtTheDateTo]
to read

Between [forms]![Form1]!Mystr1 And [forms]![Form1]!MyStr2

This doesn't work though.
Where
MyStr1 = VBA.Mid(Me.dtDateFrom.Value, 1, 10)
MyStr2 = VBA.Mid(Me.dtDateTo.Value, 1, 10)
MyStr1 and MyStr2 are variables behind the form Form1, the query gets run after a button on Form1 is pressed. The form doesn't close when the button is pressed.

Do I have to declare MyStr1 and MyStr2 as Global Variables then call them froma function?. http://vbaexpress.com/forum/images/smilies/102.gif
If so, what should the query criteria and function read like?.
Thanks.

[Tommy]

Hi Marcster,

I think this may be what you are looking for. I think you are going from a textbox on a form to a variable, which eliminates the need for [forms]![Form1]!

Between Mystr1 And MyStr2

HTH

[Marcster]

Hi Tommy :hi: ,

I've tried your suggestion but Access throughs up this error message:

Run-time error 3071:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Any ideas? :help ,

Thanks,

Marcster.

[jadedata]

The dates in that sql statement must be properly data-typed for that method to work.

I suggest you build the whole sql string as a variable so that you can syntax check it visually or with the query engine.

dim ssql as string, swhere as string
swhere = "WHERE [MyField1] BETWEEN #" & MyStr1 & "& and #" & MyStr2 & "#"

ssql = "SELECT * FROM MyTable1 " & swhere

[Marcster]

Hello jadedata,
I've tried this:
Dim ssql As String, swhere As String
swhere = "WHERE [DateOfComplaint] BETWEEN #" & MyStr1 & "& and #" & MyStr2 & "#"
ssql = "SELECT * FROM tblMainData " & swhere
DoCmd.RunSQL ssql

DateOfComplaint is now a field name formatted as a Long Date in a table named tblMainData

But I get this error:

Run-time error: 3075
Syntax error (missing operator) in query expression
'[DateOfComplaint] BETWEEN #05/11/2004& and #24/11/2004#'.

How to fix?,
Thanks,
Marcster.

[Norie]

Marc

You seem to be missing a # after the first date.

[Marcster]

Hi Norie,

Changed code to:

Dim ssql As String, swhere As String
swhere = "WHERE [DateOfComplaint] BETWEEN #" & MyStr1 & "# and #" & MyStr2 & "#"
ssql = "SELECT * FROM tblMainData " & swhere

DoCmd.RunSQL ssql

But have error:
Run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement.

What should the ssql be typed like?.

Thanks,

Marcster.

[jadedata]

Syntax error (missing operator) in query expression
you swapped a #, for a &

[xCav8r]

A personal preference of mine--aimed at avoiding mistakes like missing ## and ' '--is to employ helper functions to enclose my criteria.

Function SingleEnquoteString(StringToEnquote As String) As String
SingleEnquoteString = "'" & StringToEnquote & "'"
End Function

Function EmpoundDate(DateToEmpound As Date) As String
EmpoundDate = "#" & DateToEmpound & "#"
End Function

Function BetweenDates(StartDate As Date, EndDate As Date) As String
BetweenDates = "BETWEEN " & EmpoundDate(StartDate) & " AND " _
& EmpoundDate(EndDate)
End Function

Sub MarcstersQuery()

Dim datStartDate As Date
Dim datEndDate As Date

Dim strSelect As String
Dim strSQL As String
Dim strWhere As String

' NB: The following dates are American formats

datStartDate = #9/5/2005#
datEndDate = #9/26/2005#

' Note: It's a good habit to use your spaces consistently. In this
' example, all of the blank spaces are at the end of each string.

strSelect = "SELECT * FROM tblMainData "
strWhere = "WHERE tblMainData.DateOfComplaint " _
& BetweenDates(datStartDate, datEndDate) & ";"
strSQL = strSelect & strWhere

Debug.Print strSQL

End Sub

Result:


SELECT * FROM tblMainData WHERE tblMainData.DateOfComplaint BETWEEN #9/5/2005# AND #9/6/2005#

[Marcster]

Thanks people :thumb .

I've managed to do what I intended now, with help from you.

Thanks again,

Marcster.