Re: Multi Field SQL Where Clause

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.)

Hank@ieultd wrote:
Steves, Thanks for replying!!!
Below is and example of one of the formats I tried. I have also used long form field designations quoting the forms etc., etc., etc... to no avail. This is by no means the only form attempted. Anyway, here is a sample:
Public Function DisplayNotes()
sysNtSysCd = "PU"
sysNtAplCd = "OHNT "
sysNtCoNo = sysPUOHCoNo
sysNtDvNo = sysPUOHDvNo
sysNtLcCd = sysPUOHLcCd
sysNtApLkNo = sysPUOHNo
sysNtApLkLn = 0
sysNtLn = 1
' NotesSelectCriteria = ([NtSysCd] = [sysNtSysCd] And [NtAplCd] = [sysNtAplCd] And [NtCoNo] = [sysNtCoNo] And [NtDvNo] = [sysNtDvNo] And [NtLcCd] = [sysNtLcCd] And [NtApLkNo] = [sysNtApLkNo] And [NtApLkLn] = [sysNtApLkLn])
sysform = "NtMaint"
DoCmd.OpenForm sysform
End Function

I tried the above "NotesSelectionCriteria" string as a filter, where clause and args both in the openform command and the gotorecord command. None work and I get the invalid type error on some occasions.
thanks for your help!!!
hank


"SteveS" wrote:


It would be helpful to see the DoCmd.OpenForm statements you tried.

What code are you using to build the where clause?


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

Hank@ieultd wrote:

I have seven key fields in a notes table so the table can be accessed from any application within mutiple systems. I have tried everything I know to perform a DoCmd.OpenForm with a mutiple field Filter/Where/Args statement. When the open is performed, the module being opened always is at the first record in the file and has ignored the selection criteria. Example: SysCd/AplCd/CoNo/DvNo/LcCd/ApLkNo/ApLkLn are the key fields and the work fields being passed are all prefixed with sys and defined in a base module at the front end of each Application Project. The Notes Maintenance Form is copied to each Application Project as a common module to all Application Projects. Is Access/VB unable to do multi field comparisons via the open args/filter and/or SQL Where. If anyone has a good example of a multi Field SQL Where selection string please fwd. Thanks Hank

.

Loading