Updating form based on 2 fields.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is the code I have:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "' And '"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

The problem is its giving me an end of statement error before the final And.
With this final bit of code my program may finally be done (I hope). This is
more advanced programming then I am used to, or was ever taught in school. So
please forgive me for my constant, and what may appear repetitive questions.
For the life of me I cannot figure out why it is throwing up an end of
statement there. I have txtStartDate, txtEndDate, and cmbCustName on a form.
When the user inputs data into these fields, and clicks on a button it runs
these parameters through a query called Query_by_Customer_and_Date and opens
the form Customer_and_Date_Weekly_Form. Any help in straightening out this
bit of code is greatly appreciated as always.

C_Ascheman
 
Try using # as the delimiter around the dates, and using the quote delimiter
only around the literal value to match the Customer field to:

strPara = "([Query_by_Customer_and_Date]![Date] Between #" & _
txtStartDate & "# And #" & txtEndDate & _
"#) AND ([Query_by_Customer_and_Date]![Customer] ='" & _
cmbCustName & "')"

That field named Date will cause you problems in some contexts (probably not
this one.)
 
Sorry Allen I should have clarified more. Everything is set to text. The
dates are text with the short date format. This bit of code:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "'"

Works fine. Its when I add this code to the end of it:

And '"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

That is gives me and end of statement error. I am already using the first
part of the code in my program, and that part tests perfectly. Its when I try
adding the query for the Customer name that it stops wanting to work. I kept
everything in text format to simplify things for myself since I am in essence
learning this as I go along .. plus the fact that my boss wants the program
up an running yesterday it's helps me to save time to keep everything as
text. At a later date I can recode it all to properly handle dates, and such.
Right now though text format is quicker, and easier to learn. Thanks for you
help so far, any more suggestions or ideas are appreciated.

C_Ascheman
Allen Browne said:
Try using # as the delimiter around the dates, and using the quote delimiter
only around the literal value to match the Customer field to:

strPara = "([Query_by_Customer_and_Date]![Date] Between #" & _
txtStartDate & "# And #" & txtEndDate & _
"#) AND ([Query_by_Customer_and_Date]![Customer] ='" & _
cmbCustName & "')"

That field named Date will cause you problems in some contexts (probably not
this one.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C_Ascheman said:
Here is the code I have:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "' And '"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

The problem is its giving me an end of statement error before the final
And.
With this final bit of code my program may finally be done (I hope). This
is
more advanced programming then I am used to, or was ever taught in school.
So
please forgive me for my constant, and what may appear repetitive
questions.
For the life of me I cannot figure out why it is throwing up an end of
statement there. I have txtStartDate, txtEndDate, and cmbCustName on a
form.
When the user inputs data into these fields, and clicks on a button it
runs
these parameters through a query called Query_by_Customer_and_Date and
opens
the form Customer_and_Date_Weekly_Form. Any help in straightening out this
bit of code is greatly appreciated as always.

C_Ascheman
Click to expand...
Click to expand...
 
strPara = "([Query_by_Customer_and_Date]![Date] Between """ & _
txtStartDate & """ And """ & txtEndDate & _
""") AND ([Query_by_Customer_and_Date]![Customer] =""" & _
cmbCustName & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C_Ascheman said:
Sorry Allen I should have clarified more. Everything is set to text. The
dates are text with the short date format. This bit of code:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "'"

Works fine. Its when I add this code to the end of it:

And '"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

That is gives me and end of statement error. I am already using the first
part of the code in my program, and that part tests perfectly. Its when I
try
adding the query for the Customer name that it stops wanting to work. I
kept
everything in text format to simplify things for myself since I am in
essence
learning this as I go along .. plus the fact that my boss wants the
program
up an running yesterday it's helps me to save time to keep everything as
text. At a later date I can recode it all to properly handle dates, and
such.
Right now though text format is quicker, and easier to learn. Thanks for
you
help so far, any more suggestions or ideas are appreciated.

C_Ascheman
Allen Browne said:
Try using # as the delimiter around the dates, and using the quote
delimiter
only around the literal value to match the Customer field to:

strPara = "([Query_by_Customer_and_Date]![Date] Between #" & _
txtStartDate & "# And #" & txtEndDate & _
"#) AND ([Query_by_Customer_and_Date]![Customer] ='" & _
cmbCustName & "')"

That field named Date will cause you problems in some contexts (probably
not
this one.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C_Ascheman said:
Here is the code I have:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" &
txtStartDate &
"' And '" & txtEndDate & "' And
'"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

The problem is its giving me an end of statement error before the final
And.
With this final bit of code my program may finally be done (I hope).
This
is
more advanced programming then I am used to, or was ever taught in
school.
So
please forgive me for my constant, and what may appear repetitive
questions.
For the life of me I cannot figure out why it is throwing up an end of
statement there. I have txtStartDate, txtEndDate, and cmbCustName on a
form.
When the user inputs data into these fields, and clicks on a button it
runs
these parameters through a query called Query_by_Customer_and_Date and
opens
the form Customer_and_Date_Weekly_Form. Any help in straightening out
this
bit of code is greatly appreciated as always.

C_Ascheman
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen that was exactly what I needed. I have a question though. When I
used this code:


strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "'"

I used "' and '" thoughout it. With the code you gave me its "" through out.
Can you please explain to me why its like that.

C_Ascheman

Allen Browne said:
strPara = "([Query_by_Customer_and_Date]![Date] Between """ & _
txtStartDate & """ And """ & txtEndDate & _
""") AND ([Query_by_Customer_and_Date]![Customer] =""" & _
cmbCustName & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C_Ascheman said:
Sorry Allen I should have clarified more. Everything is set to text. The
dates are text with the short date format. This bit of code:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "'"

Works fine. Its when I add this code to the end of it:

And '"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

That is gives me and end of statement error. I am already using the first
part of the code in my program, and that part tests perfectly. Its when I
try
adding the query for the Customer name that it stops wanting to work. I
kept
everything in text format to simplify things for myself since I am in
essence
learning this as I go along .. plus the fact that my boss wants the
program
up an running yesterday it's helps me to save time to keep everything as
text. At a later date I can recode it all to properly handle dates, and
such.
Right now though text format is quicker, and easier to learn. Thanks for
you
help so far, any more suggestions or ideas are appreciated.

C_Ascheman
Allen Browne said:
Try using # as the delimiter around the dates, and using the quote
delimiter
only around the literal value to match the Customer field to:

strPara = "([Query_by_Customer_and_Date]![Date] Between #" & _
txtStartDate & "# And #" & txtEndDate & _
"#) AND ([Query_by_Customer_and_Date]![Customer] ='" & _
cmbCustName & "')"

That field named Date will cause you problems in some contexts (probably
not
this one.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the code I have:

strPara = "[Query_by_Customer_and_Date]![Date] Between '" &
txtStartDate &
"' And '" & txtEndDate & "' And
'"[Query_by_Customer_and_Date]![Customer]
Like '" & cmbCustName & "'"

The problem is its giving me an end of statement error before the final
And.
With this final bit of code my program may finally be done (I hope).
This
is
more advanced programming then I am used to, or was ever taught in
school.
So
please forgive me for my constant, and what may appear repetitive
questions.
For the life of me I cannot figure out why it is throwing up an end of
statement there. I have txtStartDate, txtEndDate, and cmbCustName on a
form.
When the user inputs data into these fields, and clicks on a button it
runs
these parameters through a query called Query_by_Customer_and_Date and
opens
the form Customer_and_Date_Weekly_Form. Any help in straightening out
this
bit of code is greatly appreciated as always.

C_Ascheman
Click to expand...
Click to expand...
Click to expand...
 
In the context of a query string, you are allowed to use the single-quote as
the delimiter for text within a string.

But if you do, the code fails when you come across a name that contains an
apostrophy, e.g.:
O'Brien and Associates

The double-quote delimiter still fails if the string contains double-quotes,
but that is much rarer. (You rarely find it except as an abbreviation for
inches or seconds.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C_Ascheman said:
Thanks Allen that was exactly what I needed. I have a question though.
When I
used this code:


strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "'"

I used "' and '" thoughout it. With the code you gave me its "" through
out.
Can you please explain to me why its like that.

C_Ascheman

Allen Browne said:
strPara = "([Query_by_Customer_and_Date]![Date] Between """ & _
txtStartDate & """ And """ & txtEndDate & _
""") AND ([Query_by_Customer_and_Date]![Customer] =""" & _
cmbCustName & """)"
Click to expand...
Click to expand...
 
Thanks Allen. That helped with understanding the why.

C_Ascheman

Allen Browne said:
In the context of a query string, you are allowed to use the single-quote as
the delimiter for text within a string.

But if you do, the code fails when you come across a name that contains an
apostrophy, e.g.:
O'Brien and Associates

The double-quote delimiter still fails if the string contains double-quotes,
but that is much rarer. (You rarely find it except as an abbreviation for
inches or seconds.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

C_Ascheman said:
Thanks Allen that was exactly what I needed. I have a question though.
When I
used this code:


strPara = "[Query_by_Customer_and_Date]![Date] Between '" & txtStartDate &
"' And '" & txtEndDate & "'"

I used "' and '" thoughout it. With the code you gave me its "" through
out.
Can you please explain to me why its like that.

C_Ascheman

Allen Browne said:
strPara = "([Query_by_Customer_and_Date]![Date] Between """ & _
txtStartDate & """ And """ & txtEndDate & _
""") AND ([Query_by_Customer_and_Date]![Customer] =""" & _
cmbCustName & """)"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top