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" wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:3ED57BDF-1DCB-4236-BB6D-(E-Mail Removed)...
> >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.
> > --
> > BJM
> > ACE Assistant
> > Gary Allan High School
>
>
>
|
Similar Threads
