Data Type mismatch error

  • Thread starter Thread starter B. Meincke
  • Start date Start date
B

B. Meincke

I am trying to use the following DCount to determine if a record exists in a
table named, tblOHList and, if not, to use an update query to write the
record, taking field values from unbound textbox controls on the frmOHL form:

DCount("*", "tblOHList", "[Municipality]='" & [Forms]![frmOHL]![CITY] & "'
And [Unit No]='" &

[Forms]![frmOHL]![Unit] & "' And [Street No]= '& [Forms]![frmOHL]![StNo]'
And [Street Name]='" &

[Forms]![frmOHL]![STREET] & "'")

I have checked to make sure the data types of each field in the table
matches the corresponding query field type and the form fied type.

Municipality/CITY, Unit No/Unit, and Street Name/STREET are all text. Street
No/StNo are numeric.

I think the problem is that, like a lot of times, I have turned to these
newsgroups for help and copied and pasted syntax and revised it to fit my
needs. This time I am at a total loss as to what I am overlooking. I have
read a number of threads on the subject and have checked the data types and
made the four fields being checked required to ensure there are no null
values involved.

If it would help I can post the SQL for the query and the full click event
syntax for the form.

Any assistance in repairing this code would be greatly appreciated. Thanks
in advance for any help.
 
Hi BJM,
there is a quotes problem with the field for street no which I assume is a
number data type.
Replace this line
--> [Street No]= '& [Forms]![frmOHL]![StNo]'

with
-->[Street No]= "& [Forms]![frmOHL]![StNo] & "

Note how the single quote has been replaced with a double quote for a number
field


For what it's worth I usually write my criteria for DCount or DLookup like
this:

Dim strCriteria As String

strCriteria = "[Municipality]= """ & [Forms]![frmOHL]![City] & """"

strCriteria = strCriteria & " And [Unit No]= """ & [Forms]![frmOHL]![Unit] &
""""

strCriteria = strCriteria & " And [Street No]= " & [Forms]![frmOHL]![StNo] &
""

strCriteria = strCriteria & " And [Street Name]= """ &
[Forms]![frmOHL]![STREET] & """"

Then I can go
DCount("*", "tblOHList", strCriteria)
I find it is much easier to write and check that the quotes are correct when
I do it like this.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
B. Meincke said:
I am trying to use the following DCount to determine if a record exists in a
table named, tblOHList and, if not, to use an update query to write the
record, taking field values from unbound textbox controls on the frmOHL form:

DCount("*", "tblOHList", "[Municipality]='" & [Forms]![frmOHL]![CITY] & "'
And [Unit No]='" &

[Forms]![frmOHL]![Unit] & "' And [Street No]= '& [Forms]![frmOHL]![StNo]'
And [Street Name]='" &

[Forms]![frmOHL]![STREET] & "'")

I have checked to make sure the data types of each field in the table
matches the corresponding query field type and the form fied type.

Municipality/CITY, Unit No/Unit, and Street Name/STREET are all text. Street
No/StNo are numeric.

I think the problem is that, like a lot of times, I have turned to these
newsgroups for help and copied and pasted syntax and revised it to fit my
needs. This time I am at a total loss as to what I am overlooking. I have
read a number of threads on the subject and have checked the data types and
made the four fields being checked required to ensure there are no null
values involved.

If it would help I can post the SQL for the query and the full click event
syntax for the form.
Click to expand...


Remove the apostrophes around tho number type field. Only
Text field values can/must be quoted.

It would be more efficient to concatenate the street number
value into the where expression instead of making Access
figure it out.

I think you want that to be more like:

...., "Municipality='" & Me.CITY & "' And [Unit No]='" _
& Me.Unit & "' And [Street No]= " & Me.StNo _
& " And [Street Name]='" & Me.STREET & "' ")

OTOH, in general a street number is not necessarily a
number. My address used to be 0S425 so maybe it should be a
Text field too.
 
Thank you...thank you...thank you, Jeanette. You are awesome! And thank you
for replying so quickly.

I'm a little embarrassed at how simple the fix turned out to be, but I just
couldn't see it. I'm sure that was because I didn't fully understand the
syntax of my mimicked and somewhat convoluted function. I've read through a
response by Klatuu to another post and along with your suggestions have
finally made the form work and, just as importantly, understand how it does.

I can't tell you how valuable this forum has been to me. Thank you all!
--
BJM
ACE Assistant
Gary Allan High School


Jeanette Cunningham said:
Hi BJM,
there is a quotes problem with the field for street no which I assume is a
number data type.
Replace this line
--> [Street No]= '& [Forms]![frmOHL]![StNo]'

with
-->[Street No]= "& [Forms]![frmOHL]![StNo] & "

Note how the single quote has been replaced with a double quote for a number
field


For what it's worth I usually write my criteria for DCount or DLookup like
this:

Dim strCriteria As String

strCriteria = "[Municipality]= """ & [Forms]![frmOHL]![City] & """"

strCriteria = strCriteria & " And [Unit No]= """ & [Forms]![frmOHL]![Unit] &
""""

strCriteria = strCriteria & " And [Street No]= " & [Forms]![frmOHL]![StNo] &
""

strCriteria = strCriteria & " And [Street Name]= """ &
[Forms]![frmOHL]![STREET] & """"

Then I can go
DCount("*", "tblOHList", strCriteria)
I find it is much easier to write and check that the quotes are correct when
I do it like this.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



B. Meincke said:
I am trying to use the following DCount to determine if a record exists in
a
table named, tblOHList and, if not, to use an update query to write the
record, taking field values from unbound textbox controls on the frmOHL
form:

DCount("*", "tblOHList", "[Municipality]='" & [Forms]![frmOHL]![CITY] & "'
And [Unit No]='" &

[Forms]![frmOHL]![Unit] & "' And [Street No]= '& [Forms]![frmOHL]![StNo]'
And [Street Name]='" &

[Forms]![frmOHL]![STREET] & "'")

I have checked to make sure the data types of each field in the table
matches the corresponding query field type and the form fied type.

Municipality/CITY, Unit No/Unit, and Street Name/STREET are all text.
Street
No/StNo are numeric.

I think the problem is that, like a lot of times, I have turned to these
newsgroups for help and copied and pasted syntax and revised it to fit my
needs. This time I am at a total loss as to what I am overlooking. I have
read a number of threads on the subject and have checked the data types
and
made the four fields being checked required to ensure there are no null
values involved.

If it would help I can post the SQL for the query and the full click event
syntax for the form.

Any assistance in repairing this code would be greatly appreciated. Thanks
in advance for any help.
Click to expand...
Click to expand...
 
Back
Top