Trouble In Paradise: Help with mismatch error

[Rambo]

Hi,

For the following line of code I keep getting a mismatch error and I am
not sure why
I am trying to set up a button so that it previews a progress report
based on two criteria, an employee ID and a date. Currently on_Click
of my "Preview Report" button I have the following code

Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"
strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "
and
"[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

DoCmd.OpenReport strReportName, acViewPreview, ,strCriteria

where [strEmployeeID] is my employee id field and [dtmDateofProgress]
is my date field

Can anyone help out?

Thanks so much.

Sincerely,
Rambo

 

[Duane Hookom]

The "and" must be inside the quotes. Also, you don't want to include extra
space between your single and double quotes. Assuming EmployeeID is text and
dtmdateOfProfress is a date, try:

strCriteria = "[strEmployeeID] = '" & Me![strEmployeeID] & "' and " & _
"[dtmDateofProgress] = #" & Me![dtmDateofProgress] & "#"

The above should work well in US locals. You may need to make some changes
if your date format is not m/d/yyyy.

 

[Phillip Windell]

Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"

^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "

Isn't EmployeeID a number? If so, it should not have single-quotes around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]

and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a Date
Type,..not a string. I believe Date Types are supposed to be enclosed
inside # when in an SQL query. There may be other syntax errors too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

 

[Rambo]

Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"

^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "

Isn't EmployeeID a number? If so, it should not have single-quotes around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]

and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a Date
Type,..not a string. I believe Date Types are supposed to be enclosed
inside # when in an SQL query. There may be other syntax errors too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error. I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo

 

[Phillip Windell]

You need to build debugging features into the form or use the script
debugging tool. You need to verify what is being "produced" as the value
for strCriteria before you actually use it.

For example.
Add a "debug" textbox to the form and make it long enough to view the whole
value
Comment out the "DoCmd.OpenReport" line.
Then just above it add
"debugbox.text = strCriteria"

Run the form, make sure you get the right expected value showing in the text
box. When it is correct delete the textbox and remove the extra code.

I'm sure there are other ways to accomplish the same thing but it's all I
could think of off the top of my head. I'm from the ASP Classic "world" and
that is how we would handle those types of things with ASP Classic.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"

^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "

Isn't EmployeeID a number? If so, it should not have single-quotes around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]

and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a Date
Type,..not a string. I believe Date Types are supposed to be enclosed
inside # when in an SQL query. There may be other syntax errors too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least
something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error. I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo

 

[Duane Hookom]

Phillip,
Great tips. Actually in Access VBA, use:
MsgBox "strCriteria: " & strCriteria
or
Debug.Print "strCriteria: " & strCriteria
The debug results can be viewed by pressing Ctrl+G

Rambo,
When something doesn't work, come back with your exact code and exact error
message. Apply the debug techique and also provide us the the results from
the debug.print statement.


--
Duane Hookom
MS Access MVP

You need to build debugging features into the form or use the script
debugging tool. You need to verify what is being "produced" as the value
for strCriteria before you actually use it.

For example.
Add a "debug" textbox to the form and make it long enough to view the
whole value
Comment out the "DoCmd.OpenReport" line.
Then just above it add
"debugbox.text = strCriteria"

Run the form, make sure you get the right expected value showing in the
text box. When it is correct delete the textbox and remove the extra code.

I'm sure there are other ways to accomplish the same thing but it's all I
could think of off the top of my head. I'm from the ASP Classic "world"
and that is how we would handle those types of things with ASP Classic.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"
^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "

Isn't EmployeeID a number? If so, it should not have single-quotes
around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]


and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a Date
Type,..not a string. I believe Date Types are supposed to be enclosed
inside # when in an SQL query. There may be other syntax errors too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least
something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error. I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo

 

[Rambo]

Hey Guys,

Thanks for sticking with me. I will give this a shot today and get
back to you.

Sincerely,
Rambo

Phillip,
Great tips. Actually in Access VBA, use:
MsgBox "strCriteria: " & strCriteria
or
Debug.Print "strCriteria: " & strCriteria
The debug results can be viewed by pressing Ctrl+G

Rambo,
When something doesn't work, come back with your exact code and exact error
message. Apply the debug techique and also provide us the the results from
the debug.print statement.


--
Duane Hookom
MS Access MVP

You need to build debugging features into the form or use the script
debugging tool. You need to verify what is being "produced" as the value
for strCriteria before you actually use it.

For example.
Add a "debug" textbox to the form and make it long enough to view the
whole value
Comment out the "DoCmd.OpenReport" line.
Then just above it add
"debugbox.text = strCriteria"

Run the form, make sure you get the right expected value showing in the
text box. When it is correct delete the textbox and remove the extra code.

I'm sure there are other ways to accomplish the same thing but it's all I
could think of off the top of my head. I'm from the ASP Classic "world"
and that is how we would handle those types of things with ASP Classic.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Phillip Windell wrote:
Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"
^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "

Isn't EmployeeID a number? If so, it should not have single-quotes
around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]


and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a Date
Type,..not a string. I believe Date Types are supposed to be enclosed
inside # when in an SQL query. There may be other syntax errors too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least
something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "


--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error. I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo

 

[Rambo]

Hey,

So I got the criteria function to work so thank you so much for your
help!
I have another question though. I am trying to get my above mentioned
report to export to word. Do you happen to know the code to do that?

Sincerely,
Rambo

Hey Guys,

Thanks for sticking with me. I will give this a shot today and get
back to you.

Sincerely,
Rambo

Phillip,
Great tips. Actually in Access VBA, use:
MsgBox "strCriteria: " & strCriteria
or
Debug.Print "strCriteria: " & strCriteria
The debug results can be viewed by pressing Ctrl+G

Rambo,
When something doesn't work, come back with your exact code and exact error
message. Apply the debug techique and also provide us the the results from
the debug.print statement.


--
Duane Hookom
MS Access MVP

You need to build debugging features into the form or use the script
debugging tool. You need to verify what is being "produced" as the value
for strCriteria before you actually use it.

For example.
Add a "debug" textbox to the form and make it long enough to view the
whole value
Comment out the "DoCmd.OpenReport" line.
Then just above it add
"debugbox.text = strCriteria"

Run the form, make sure you get the right expected value showing in the
text box. When it is correct delete the textbox and remove the extra code.

I'm sure there are other ways to accomplish the same thing but it's all I
could think of off the top of my head. I'm from the ASP Classic "world"
and that is how we would handle those types of things with ASP Classic.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com


Phillip Windell wrote:
Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"
^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " ' "

Isn't EmployeeID a number? If so, it should not have single-quotes
around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]


and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a Date
Type,..not a string. I believe Date Types are supposed to be enclosed
inside # when in an SQL query. There may be other syntax errors too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least
something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "


--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error. I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo

 

[Duane Hookom]

I just use the menu system to "Office Link" to Word.

--
Duane Hookom
MS Access MVP

Hey,

So I got the criteria function to work so thank you so much for your
help!
I have another question though. I am trying to get my above mentioned
report to export to word. Do you happen to know the code to do that?

Sincerely,
Rambo

Hey Guys,

Thanks for sticking with me. I will give this a shot today and get
back to you.

Sincerely,
Rambo

Phillip,
Great tips. Actually in Access VBA, use:
MsgBox "strCriteria: " & strCriteria
or
Debug.Print "strCriteria: " & strCriteria
The debug results can be viewed by pressing Ctrl+G

Rambo,
When something doesn't work, come back with your exact code and exact
error
message. Apply the debug techique and also provide us the the results
from
the debug.print statement.


--
Duane Hookom
MS Access MVP



You need to build debugging features into the form or use the script
debugging tool. You need to verify what is being "produced" as the
value
for strCriteria before you actually use it.

For example.
Add a "debug" textbox to the form and make it long enough to view the
whole value
Comment out the "DoCmd.OpenReport" line.
Then just above it add
"debugbox.text = strCriteria"

Run the form, make sure you get the right expected value showing in
the
text box. When it is correct delete the textbox and remove the extra
code.

I'm sure there are other ways to accomplish the same thing but it's
all I
could think of off the top of my head. I'm from the ASP Classic
"world"
and that is how we would handle those types of things with ASP
Classic.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com


Phillip Windell wrote:
Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"
^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " '
"

Isn't EmployeeID a number? If so, it should not have single-quotes
around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]


and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a
Date
Type,..not a string. I believe Date Types are supposed to be
enclosed
inside # when in an SQL query. There may be other syntax errors
too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least
something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "


--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error.
I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo

 

[Rambo]

Dear Mr. Hookom,

That works just fine. You have been a great help to me. Thank you
very much.

Sincerely,
Rambo

I just use the menu system to "Office Link" to Word.

--
Duane Hookom
MS Access MVP

Hey,

So I got the criteria function to work so thank you so much for your
help!
I have another question though. I am trying to get my above mentioned
report to export to word. Do you happen to know the code to do that?

Sincerely,
Rambo

Hey Guys,

Thanks for sticking with me. I will give this a shot today and get
back to you.

Sincerely,
Rambo
Duane Hookom wrote:
Phillip,
Great tips. Actually in Access VBA, use:
MsgBox "strCriteria: " & strCriteria
or
Debug.Print "strCriteria: " & strCriteria
The debug results can be viewed by pressing Ctrl+G

Rambo,
When something doesn't work, come back with your exact code and exact
error
message. Apply the debug techique and also provide us the the results
from
the debug.print statement.


--
Duane Hookom
MS Access MVP



You need to build debugging features into the form or use the script
debugging tool. You need to verify what is being "produced" as the
value
for strCriteria before you actually use it.

For example.
Add a "debug" textbox to the form and make it long enough to view the
whole value
Comment out the "DoCmd.OpenReport" line.
Then just above it add
"debugbox.text = strCriteria"

Run the form, make sure you get the right expected value showing in
the
text box. When it is correct delete the textbox and remove the extra
code.

I'm sure there are other ways to accomplish the same thing but it's
all I
could think of off the top of my head. I'm from the ASP Classic
"world"
and that is how we would handle those types of things with ASP
Classic.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com


Phillip Windell wrote:
Private Sub cmdPreviewRecord_Click()
Dim strReportName As String
Dim strCriteria As String

str ReportName = "rptProgressReport"
^--that space should not be there.

strCriteria = "[strEmployeeID] = ' " & Me![strEmployeeID] & " '
"

Isn't EmployeeID a number? If so, it should not have single-quotes
around
it.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID]


and "[dtmDateofProgress] = ' " & Me![dtmDateofProgress] & " ' "

dtmDateOfProgress should not have single-quotes around it,..it is a
Date
Type,..not a string. I believe Date Types are supposed to be
enclosed
inside # when in an SQL query. There may be other syntax errors
too.

and "[dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "

I could be wrong, but maybe it should look like this, or at least
something
similar.

strCriteria = "[strEmployeeID] = " & Me![strEmployeeID] & _
"and [dtmDateofProgress] = # " & Me![dtmDateofProgress] & " # "


--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

In this case strEmployeeID is a text field.

I have tried both ways and still can't seem to get rid of the error.
I
will keep on trying things out and if anyone comes up with any more
suggestions I would be more than happy to try those too. Thanks for
the effort though guys it is very much appreciated.

Sincerely,
Rambo