pass paramter to union query using VBA code

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

strSql = " SELECT DISTINCT MyUnionQuery.MyField FROM MyUnionQuery WHERE " & _

" MyUnionquery.MyCode Is Not Null " & _
" and MyUnionQuery.[Activity Date] between " & _
" [Forms]![frmmyform]![dtpStartDate] and
[Forms]![frmMyForm]![dtpEndDate];"

I have one union query which accepts 2 parameter from my form.
The query works when the form is opened.

I need run the query from VBA code.
I got too few paramters message when I run above VBA code.
How should I pass parameter to union query?

should I use db.parameter("[Forms]![frmmyform]![dtpStartDate]") =
[Forms]![frmmyform]![dtpStartDate]? to run the query by VBA?

Your information is great appreciated,
 
Put the references to the form controls outside of the quotes (and, since
they're dates, make sure they're properly formatted):

strSql = " SELECT DISTINCT MyUnionQuery.MyField FROM MyUnionQuery WHERE " &
_
" MyUnionquery.MyCode Is Not Null " & _
" and MyUnionQuery.[Activity Date] between " & _
Format([Forms]![frmmyform]![dtpStartDate], "\#yyyy\-mm\-dd\#") & _
" and " & _
Format([Forms]![frmMyForm]![dtpEndDate], "\#yyyy\-mm\-dd\#")
 
Thanks millions,

Douglas J. Steele said:
Put the references to the form controls outside of the quotes (and, since
they're dates, make sure they're properly formatted):

strSql = " SELECT DISTINCT MyUnionQuery.MyField FROM MyUnionQuery WHERE " &
_
" MyUnionquery.MyCode Is Not Null " & _
" and MyUnionQuery.[Activity Date] between " & _
Format([Forms]![frmmyform]![dtpStartDate], "\#yyyy\-mm\-dd\#") & _
" and " & _
Format([Forms]![frmMyForm]![dtpEndDate], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Souris said:
strSql = " SELECT DISTINCT MyUnionQuery.MyField FROM MyUnionQuery WHERE "
& _

" MyUnionquery.MyCode Is Not Null " & _
" and MyUnionQuery.[Activity Date] between " & _
" [Forms]![frmmyform]![dtpStartDate] and
[Forms]![frmMyForm]![dtpEndDate];"

I have one union query which accepts 2 parameter from my form.
The query works when the form is opened.

I need run the query from VBA code.
I got too few paramters message when I run above VBA code.
How should I pass parameter to union query?

should I use db.parameter("[Forms]![frmmyform]![dtpStartDate]") =
[Forms]![frmmyform]![dtpStartDate]? to run the query by VBA?

Your information is great appreciated,
Click to expand...
Click to expand...
 
Back
Top