Re: Multi Field SQL Where Clause

Hello Again Steve;
I haven't given up yet of course so here goes.
The triple quotes cause a syntax error. When I remove the two additional
quotes the syntax error goes away but the functionality is still absent.
Do the definitions have to be DIM rather than PUBLIC as in
Public sysNtCoNo as Single
Public sysNtDvNo as Single
Public sysNtLcCd as Single
Public sysNtApLkNo as Long
etc.
Note the co/dv & lc are single fixed fields. I don't know if that makes a
difference with the quotes within the string or not.
Also note, when using the string as you have defined it the correct values
are displayed so it has to be close to correct. Just can't identify the
obvious probably.
Thanks Again for all your help!!!
hank

"SteveS" wrote:

Hi Hank,

First, open a code window, then in the menu bar, click on TOOLS/OPTIONS.

Click on the Editor tab, then UNCHECK "Auto Syntax Check". This option just
causes a dialog to appear when there is a syntax error. The error turns red,
so there is no need for the additional warning (in my opinion).

Next, CHECK "Require Variable Declaration". This adds a line to any NEW module
created that requires variables to be DIM'ed. Any new module will have as the
first two lines:

Option Compare Database
Option Explicit



Now for the code.
I can tell that "sysNtSysCd" and "sysNtAplCd" are strings and "sysNtApLkLn" and
"sysNtLn" are integers.

What data types are these variables: (String, Integer, Long, etc.)

sysNtCoNo
sysNtDvNo
sysNtLcCd
sysNtApLkNo


You almost have it. The where clause needs a little different.
Look at the variable "strWhere" in the following modified code. (I changed
'NotesSelectCriteria' to 'strWhere')

(watch for line wrap)

'************************************
Option Compare Database
Option Explicit

Public Function DisplayNotes()
Dim sysNtSysCd As String
Dim sysNtAplCd As String

'this was 'NotesSelectCriteria'
Dim strWhere As String

Dim sysNtCoNo As Integer
Dim sysNtDvNo As Integer
Dim sysNtLcCd As Integer
Dim sysNtApLkNo As Integer
Dim sysNtApLkLn As Integer
Dim sysNtLn As Integer

'initialize
sysNtSysCd = "PU"
sysNtAplCd = "OHNT" '< removed a training space
sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo
sysNtApLkLn = 0
sysNtLn = 1

'create the Where clause
'these are strings
strWhere = "[NtSysCd] = '" & [sysNtSysCd] & "' And "
strWhere = strWhere & "[NtAplCd] = '" & [sysNtAplCd] & "' And "

'and these are numeric
strWhere = strWhere & "[NtCoNo] = " & [sysNtCoNo] & " And "
strWhere = strWhere & "[NtDvNo] = " & [sysNtDvNo] & " And "
strWhere = strWhere & "[NtLcCd] = " & [sysNtLcCd] & " And "
strWhere = strWhere & "[NtApLkNo] = " & [sysNtApLkNo] & " And """
strWhere = strWhere & "[NtApLkLn] = " & [sysNtApLkLn]

'------ for debugging only ------------
MsgBox NotesSelectCriteria
'---------------------------------

sysform = "NtMaint"

'syntax
'DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

DoCmd.OpenForm sysform, , , strWhere
End Function
'************************************

HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)



.

Relevant Pages

  • Re: Search for a record based on one of two fields
    ... Afraid it still does not work - it would appear to be a syntax error (the ... text comes up in red and the compiler reports a syntax error) ... > Dim stLinkCriteria As String ...
    (microsoft.public.access.formscoding)
  • Re: Multi Field SQL Where Clause
    ... Public sysNtLcCd as Single ... Public sysNtApLkNo as Long ... difference with the quotes within the string or not. ... Dim sysNtSysCd As String ...
    (microsoft.public.access.forms)
  • Re: Error 3075 Syntax Error
    ... Syntax error in query expression ... Dim mydb As Database ... Dim strQryName As String ... Dim strArchiveToDB As String ...
    (microsoft.public.access.formscoding)
  • Re: Filter issue?????
    ... into this syntax error and it driving me a little crazy. ... Dim strDeptName As String ... ' Combine criteria strings into a WHERE clause for the filter ...
    (microsoft.public.access.forms)
  • Re: Not In List
    ... Was just flicking through the msgboard and noticed a syntax error in your ... > Private Sub cboJobNumber_NotInList(NewData As String, ... > Dim strsql As String, ...
    (microsoft.public.access.forms)
Loading