Re: newbee needs help

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: tw (tw_at_discussions.microsoft.com)
Date: 03/15/05 

Date: Tue, 15 Mar 2005 09:47:09 -0800

I copied the results in the immediate window to the sql query builder window
and it worked just fine. The datatype mismatch seems to be coming from
another part of the statement

Set rs = CurrentDb.OpenRecordset(strsql)

rs is declared as a recordset (dim rs as recordset)
strsql is declared as a string (dim strsql as string)

is CurrentDb correct?

"tw" wrote:

> I had to make a change to the Forms![frm clients].[AU-FK Client ID] the field
> on the form was actually [CL-PK Client ID] but I'm still having problems.
>
> The is the result in the immediate window...
>
> Select * From [CC PCS Authorized Units] Where [Au-fk Proc id] = 'A0080' and
> [AU-FK Client ID] = 54
>
> The error I'm getting now is a type mismatch. [AU-FK Proc ID] is text and
> [au-fk client id] is number (long)
>
>
> "Ken Snell [MVP]" wrote:
>
> > Also, to go along with my just-sent reply, is it possible for the Me![au-fk
> > proc id] value to contain a ' character?
> >
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> > "tw" <tw@discussions.microsoft.com> wrote in message
> > news:A0D342B0-F3B0-498E-9DB0-14B51A247E76@microsoft.com...
> > > [au-fk client id] is a number field
> > >
> > > "Ken Snell [MVP]" wrote:
> > >
> > >> Is Au-fk Client id field a text field? If yes, delimit the value from the
> > >> form with ' characters, just as you did for the Au-fk Proc id field just
> > >> above it.
> > >>
> > >> --
> > >>
> > >> Ken Snell
> > >> <MS ACCESS MVP>
> > >>
> > >> "tw" <tw@discussions.microsoft.com> wrote in message
> > >> news:5F2F1447-5D2E-4990-BE61-5A52A613A2F8@microsoft.com...
> > >> >I made the corrections you suggested. I also had a typo in the spelling
> > >> >of
> > >> > one of the field names. Now my query string looks like this and I'm
> > >> > getting
> > >> > an error message too few parameters expected 1
> > >> >
> > >> > strsql = "Select * From [CC PCS Authorized Units] Where " & _
> > >> > "[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and "
> > >> > & _
> > >> > "[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client
> > >> > ID]
> > >> >
> > >> > thanks for you help
> > >> >
> > >> > "Ken Snell [MVP]" wrote:
> > >> >
> > >> >> One parameter likely is Forms![frm clients].[au-fk client id].
> > >> >> Concatenate
> > >> >> its value into the SQL string (excerpt shown here)
> > >> >> "[Au-pk Client id] = " & Forms![frm clients]![au-fk
> > >> >> client
> > >> >> id]
> > >> >>
> > >> >> The other parameter may be coming from the lack of a space after the
> > >> >> word
> > >> >> "and" in your string:
> > >> >> strsql = "Select * From [CC PCS Authorized Units] Where " & _
> > >> >> "[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and
> > >> >> " &
> > >> >> _
> > >> >> "[Au-pk Client id] = " & Forms![frm clients]![au-fk
> > >> >> client
> > >> >> id]
> > >> >>
> > >> >> If that isn't it, then check the spelling of the two field names in
> > >> >> your
> > >> >> string.
> > >> >>
> > >> >> --
> > >> >>
> > >> >> Ken Snell
> > >> >> <MS ACCESS MVP>
> > >> >>
> > >> >> "tw" <tw@discussions.microsoft.com> wrote in message
> > >> >> news:8EE20EE2-8E2B-4BB8-BB81-0CC706F6A8C7@microsoft.com...
> > >> >> >I have this code in a form on a date field [au from date]. I'm
> > >> >> >getting
> > >> >> >an
> > >> >> > error message at the "set rs =" statement. The error is that I have
> > >> >> > too
> > >> >> > few
> > >> >> > parameters. Expected 2. Can anyone tell me what parameters it
> > >> >> > expects?
> > >> >> >
> > >> >> > Private Sub AU_From_Date_AfterUpdate()
> > >> >> > ' see if there is a date conflict
> > >> >> > ' on any other authorizations for same client/procedure
> > >> >> > Dim rs As Recordset
> > >> >> > Dim strsql As String
> > >> >> >
> > >> >> > strsql = "Select * From [CC PCS Authorized Units] Where " & _
> > >> >> > "[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "'
> > >> >> > and" &
> > >> >> > _
> > >> >> > "[Au-pk Client id] = Forms![frm clients].[au-fk client
> > >> >> > id]"
> > >> >> >
> > >> >> > Set rs = CurrentDb.OpenRecordset(strsql)
> > >> >> >
> > >> >> > Do Until rs.EOF
> > >> >> > If rs![AU-PK] <> Me.[AU-PK] Then
> > >> >> > If Me.[au from date] >= rs![au from date] And Me.[au from
> > >> >> > date]
> > >> >> > <= rs![au to date] Then
> > >> >> > MsgBox ("Date conflicts with another authorization
> > >> >> > please
> > >> >> > check authorization and fix one of them")
> > >> >> > Exit Sub
> > >> >> > End If
> > >> >> > End If
> > >> >> > rs.MoveNext
> > >> >> > Loop
> > >> >> > End Sub
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Relevant Pages

  • Re: page margin
    ... > The problem was I didn't know what you meant by the "Immediate Window". ... > less time just copying my other report and modifying the copy. ... >> Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.reports)
  • Re: EXCEL
    ... EnableEvents which can be turned on or off. ... The immediate window is a window in the VB IDE. ... >> Bob Phillips ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Combo Box and SubForm
    ... Yes, that is the breakpoint. ... Immediate Window from the View menu at the top of the Code window). ... Copy the resulting SQL code. ...
    (microsoft.public.access.formscoding)
  • Re: Combo Box and SubForm
    ... "Carl Rapson" wrote: ... Immediate Window from the View menu at the top of the Code window). ... Copy the resulting SQL code. ...
    (microsoft.public.access.formscoding)
  • Re: Excel Export - Field Names in First Row of Spreadsheet
    ... Window visible (View | Immediate Window), and type the following into that ... <MS ACCESS MVP> ... > OK Ken - Just done that - the offending line is ...
    (microsoft.public.access.externaldata)