Opening form based on 2 values.

G

Guest

I am attempting to open a form by passing 2 values to the query that the form
is based off of. If I remove the end part of the statement it runs fine, but
if I try and attach the second part it gives me a type mismatch. Here is the
code I have:

Dim strPara As String
If chkWeekly.Value = True Then
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And
strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
DoCmd.OpenForm "Query_by_Date_Weekly_Form", acViewNormal, , strPara
Else
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And
strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
DoCmd.OpenForm "Monthly_by_Date_Form", acViewNormal, , strPara
End If

(sorry for how it looks)
Basically I am trying to pull up a form via a query based on whether the
criteria falls in between the date ranges (txtStartDate and txtEndDate). I
know its throwing the error on everything after the And in this part of the
code:

strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And strPara =
"[Query_by_Date]![Date]<='" & txtEndDate & "'"

Any help is greatly appreciated.

C_Ascheman
 
G

Guest

If the fields are dates, you need to use # between them
Try

strPara = "[Query_by_Date]![Date]>=#" & txtStartDate & "# And
[Query_by_Date]![Date]<=#" & txtEndDate & "#"

Or
strPara = "[Query_by_Date]![Date] Between #" & txtStartDate & "# And #" &
txtEndDate & "#"
 
G

Guest

No its all text, and formatted to short date. Like I said if I use just
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'"
it all works fine but is only pulling up values after the date(text) in
txtStartDate. If I try to add the last part
And strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
then it gives me a type mismatch error. Everything is text, and its all
formatted to Short Date format.


Ofer Cohen said:
If the fields are dates, you need to use # between them
Try

strPara = "[Query_by_Date]![Date]>=#" & txtStartDate & "# And
[Query_by_Date]![Date]<=#" & txtEndDate & "#"

Or
strPara = "[Query_by_Date]![Date] Between #" & txtStartDate & "# And #" &
txtEndDate & "#"

--
Good Luck
BS"D


C_Ascheman said:
I am attempting to open a form by passing 2 values to the query that the form
is based off of. If I remove the end part of the statement it runs fine, but
if I try and attach the second part it gives me a type mismatch. Here is the
code I have:

Dim strPara As String
If chkWeekly.Value = True Then
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And
strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
DoCmd.OpenForm "Query_by_Date_Weekly_Form", acViewNormal, , strPara
Else
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And
strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
DoCmd.OpenForm "Monthly_by_Date_Form", acViewNormal, , strPara
End If

(sorry for how it looks)
Basically I am trying to pull up a form via a query based on whether the
criteria falls in between the date ranges (txtStartDate and txtEndDate). I
know its throwing the error on everything after the And in this part of the
code:

strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And strPara =
"[Query_by_Date]![Date]<='" & txtEndDate & "'"

Any help is greatly appreciated.

C_Ascheman
Click to expand...
Click to expand...
 
G

Guest

Thanks Ofer. I needed to use Between in the statement. Once I added that it
is running perfectly now.

C_Ascheman

C_Ascheman said:
No its all text, and formatted to short date. Like I said if I use just
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'"
it all works fine but is only pulling up values after the date(text) in
txtStartDate. If I try to add the last part
And strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
then it gives me a type mismatch error. Everything is text, and its all
formatted to Short Date format.


Ofer Cohen said:
If the fields are dates, you need to use # between them
Try

strPara = "[Query_by_Date]![Date]>=#" & txtStartDate & "# And
[Query_by_Date]![Date]<=#" & txtEndDate & "#"

Or
strPara = "[Query_by_Date]![Date] Between #" & txtStartDate & "# And #" &
txtEndDate & "#"

--
Good Luck
BS"D


C_Ascheman said:
I am attempting to open a form by passing 2 values to the query that the form
is based off of. If I remove the end part of the statement it runs fine, but
if I try and attach the second part it gives me a type mismatch. Here is the
code I have:

Dim strPara As String
If chkWeekly.Value = True Then
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And
strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
DoCmd.OpenForm "Query_by_Date_Weekly_Form", acViewNormal, , strPara
Else
strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And
strPara = "[Query_by_Date]![Date]<='" & txtEndDate & "'"
DoCmd.OpenForm "Monthly_by_Date_Form", acViewNormal, , strPara
End If

(sorry for how it looks)
Basically I am trying to pull up a form via a query based on whether the
criteria falls in between the date ranges (txtStartDate and txtEndDate). I
know its throwing the error on everything after the And in this part of the
code:

strPara = "[Query_by_Date]![Date]>='" & txtStartDate & "'" And strPara =
"[Query_by_Date]![Date]<='" & txtEndDate & "'"

Any help is greatly appreciated.

C_Ascheman
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open form based on criteria of another form. 5
Updating form based on 2 fields. 6
sql where start/end date 7
Parameterised query to return all records or those Between two val 2
Is field between date range on form 2
Access 2003 - Setting default value of a text box 5
Chart Object 3
Want a Parameter for date range driving a cross tab query 2

Top