Multiple "And" record selection criteria

G

Guest

I am attempting to set up a screen from which users can select records using
"AND" logic. I am coming up with either "Syntax" or "Type Mismatch" errors
and I can't understand why. The cose so far is shown below. The Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy". On
entering a date in the selection screen I get a "Type MisMatch" error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " & Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
 
D

Douglas J Steele

AFAIK, there is no IsNothing function in Access, so I'm suspecting it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form isn't
already opened. The recordset you're opening has nothing to do with the
form.

What's the actual value of gstrDirView when the errors arise?
 
S

Svetlana

Try to pass your user's input into variables, change them to the right
data type according to your table fields data types then create a
select case where you gonna built all possible sql clauses even if the
user gonna leave some fields empty.
 
G

Guest

I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the learning
curve - from the Acess book by J Viscas(?). I used this in another little
application I created where I was successful in also creating one of these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I work
like to use the switchboard and do no tlike their desktops all clutered up.


I forget how ato the value fo gstrDirView - I do not have my reference book
here - but I remember it being easy. (The "Filter" on the Properties Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


Douglas J Steele said:
AFAIK, there is no IsNothing function in Access, so I'm suspecting it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form isn't
already opened. The recordset you're opening has nothing to do with the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am attempting to set up a screen from which users can select records using
"AND" logic. I am coming up with either "Syntax" or "Type Mismatch" errors
and I can't understand why. The cose so far is shown below. The Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy". On
entering a date in the selection screen I get a "Type MisMatch" error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " & Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
Click to expand...
Click to expand...
 
G

Guest

Hi Svetlana:

I am really just a beginner user, and this is a little more cryptic than I
can handle. If you could give me a little example of what you mean I may be
able to figure it out.

Thank you
 
D

Douglas J Steele

To see what the value of gstrDirView, you can put a break-point in the code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the learning
curve - from the Acess book by J Viscas(?). I used this in another little
application I created where I was successful in also creating one of these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I work
like to use the switchboard and do no tlike their desktops all clutered up.


I forget how ato the value fo gstrDirView - I do not have my reference book
here - but I remember it being easy. (The "Filter" on the Properties Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


Douglas J Steele said:
AFAIK, there is no IsNothing function in Access, so I'm suspecting it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form isn't
already opened. The recordset you're opening has nothing to do with the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message news:[email protected]...
I am attempting to set up a screen from which users can select records using
"AND" logic. I am coming up with either "Syntax" or "Type Mismatch" errors
and I can't understand why. The cose so far is shown below. The Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy". On
entering a date in the selection screen I get a "Type MisMatch" error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " & Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The actual values of gstrDirView is as follows:

[DirID] = 9
[DirID] = 11
[TargetDate] >= #01-01-2006#
[LocCust] = C1685-001 226

These return values are independent - i.e. I made 4 different independent
selections.

DirID is AutoNumber long integer,
Target date is Date/Time as mm-dd-yyyy, and
LocCust is text.

Does this help?

Thank you



Douglas J Steele said:
To see what the value of gstrDirView, you can put a break-point in the code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the learning
curve - from the Acess book by J Viscas(?). I used this in another little
application I created where I was successful in also creating one of these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I work
like to use the switchboard and do no tlike their desktops all clutered up.


I forget how ato the value fo gstrDirView - I do not have my reference book
here - but I remember it being easy. (The "Filter" on the Properties Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


Douglas J Steele said:
AFAIK, there is no IsNothing function in Access, so I'm suspecting it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form isn't
already opened. The recordset you're opening has nothing to do with the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am attempting to set up a screen from which users can select records
using
"AND" logic. I am coming up with either "Syntax" or "Type Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy". On
entering a date in the selection screen I get a "Type MisMatch" error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " &
Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I think I have isolated the problem to the situation where the Form
"FDirView" is not loaded when running the filter. When the form is opn the
correct records are returned. ( I have gotten rid of the syntax error on the
text field.

My code now looks like this:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub

Any further ideas?

Thank you


Douglas J Steele said:
To see what the value of gstrDirView, you can put a break-point in the code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the learning
curve - from the Acess book by J Viscas(?). I used this in another little
application I created where I was successful in also creating one of these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I work
like to use the switchboard and do no tlike their desktops all clutered up.


I forget how ato the value fo gstrDirView - I do not have my reference book
here - but I remember it being easy. (The "Filter" on the Properties Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


Douglas J Steele said:
AFAIK, there is no IsNothing function in Access, so I'm suspecting it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form isn't
already opened. The recordset you're opening has nothing to do with the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am attempting to set up a screen from which users can select records
using
"AND" logic. I am coming up with either "Syntax" or "Type Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy". On
entering a date in the selection screen I get a "Type MisMatch" error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " &
Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


Douglas J Steele said:
To see what the value of gstrDirView, you can put a break-point in the code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the learning
curve - from the Acess book by J Viscas(?). I used this in another little
application I created where I was successful in also creating one of these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I work
like to use the switchboard and do no tlike their desktops all clutered up.


I forget how ato the value fo gstrDirView - I do not have my reference book
here - but I remember it being easy. (The "Filter" on the Properties Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


Douglas J Steele said:
AFAIK, there is no IsNothing function in Access, so I'm suspecting it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form isn't
already opened. The recordset you're opening has nothing to do with the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am attempting to set up a screen from which users can select records
using
"AND" logic. I am coming up with either "Syntax" or "Type Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy". On
entering a date in the selection screen I get a "Type MisMatch" error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " &
Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you open the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ralph Wischnewski said:
I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


Douglas J Steele said:
To see what the value of gstrDirView, you can put a break-point in the
code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating one of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I work
like to use the switchboard and do no tlike their desktops all clutered up.


I forget how ato the value fo gstrDirView - I do not have my reference book
here - but I remember it being easy. (The "Filter" on the Properties
Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


:

AFAIK, there is no IsNothing function in Access, so I'm suspecting
it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form
isn't
already opened. The recordset you're opening has nothing to do with
the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
wrote in
message I am attempting to set up a screen from which users can select
records
using
"AND" logic. I am coming up with either "Syntax" or "Type
Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and
is
producing a "Syntax" Error message: (Missing operator)in query expresion)

The second is a date format set up in the table as "mm-dd-yyyy".
On
entering a date in the selection screen I get a "Type MisMatch"
error.

The 3rd is a auto number long integer format and also produces a "Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " &
Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" &
Me!FromDate & "#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= "
& "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me.CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am sorry - I attempted to answer both questions in one of my many responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND [DirID] = 5

The reason the form is not open is because my users like to use the menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering crieria and
then opening the results in a form or a report.

Thank you


Douglas J. Steele said:
You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you open the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ralph Wischnewski said:
I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


Douglas J Steele said:
To see what the value of gstrDirView, you can put a break-point in the
code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating one of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I
work
like to use the switchboard and do no tlike their desktops all clutered
up.


I forget how ato the value fo gstrDirView - I do not have my reference
book
here - but I remember it being easy. (The "Filter" on the Properties
Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


:

AFAIK, there is no IsNothing function in Access, so I'm suspecting
it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form
isn't
already opened. The recordset you're opening has nothing to do with
the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
wrote
in
message I am attempting to set up a screen from which users can select
records
using
"AND" logic. I am coming up with either "Syntax" or "Type
Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and
is
producing a "Syntax" Error message: (Missing operator)in query
expresion)

The second is a date format set up in the table as "mm-dd-yyyy".
On
entering a date in the selection screen I get a "Type MisMatch"
error.

The 3rd is a auto number long integer format and also produces a
"Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " &
Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" &
Me!FromDate &
"#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] >= "
&
"#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

gstrDirView looks syntactically correct to me, as does the DoCmd.OpenForm
statement. You say you've "gotten rid of the syntax error and the correct
records are returning if the form is loaded". What exactly is the problem
when the form isn't loaded?

My comment about the recordset is that you're opening the recordset rst, but
not doing anything with it. Since rst is declared inside the procedure, it's
not available anywhere else in the form, and opening a recordset has no
impact on opening a form. My recommendation is to remove all references to
db and rst in your procedure, since all it's accomplishing is taking
processing time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am sorry - I attempted to answer both questions in one of my many responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND [DirID] = 5

The reason the form is not open is because my users like to use the menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering crieria and
then opening the results in a form or a report.

Thank you


Douglas J. Steele said:
You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you open the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message news:[email protected]...
I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


:

To see what the value of gstrDirView, you can put a break-point in the
code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating one of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I
work
like to use the switchboard and do no tlike their desktops all clutered
up.


I forget how ato the value fo gstrDirView - I do not have my reference
book
here - but I remember it being easy. (The "Filter" on the Properties
Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


:

AFAIK, there is no IsNothing function in Access, so I'm suspecting
it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form
isn't
already opened. The recordset you're opening has nothing to do with
the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
wrote
in
message I am attempting to set up a screen from which users can select
records
using
"AND" logic. I am coming up with either "Syntax" or "Type
Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.

Te first is for "Location ID". this field is in text format and
is
producing a "Syntax" Error message: (Missing operator)in query
expresion)

The second is a date format set up in the table as "mm-dd-yyyy".
On
entering a date in the selection screen I get a "Type MisMatch"
error.

The 3rd is a auto number long integer format and also produces a
"Type
Mismatch" error.

Can some one help?

This is the code:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] = " & Me!CboLoc
Else
gstrDirView = gstrDirView & " AND [LocCust] = " &
Me!CboLoc
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" &
Me!FromDate &
"#"
Else
gstrDirView = gstrDirView & "AND [TargetDate] = "
&
"#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me.CboDirID) Then
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Users will be selecting thier criteria in a form called "FDialogSelDir".
After they have made thier selection I want to open the form "FDirView".
when "FDirView" is open at the time they make thier selection the appropriate
records show on "FDirView". However if "FDirView" is opened based on the
DoCmd.OpenForm I get a "Type Mismatch" error.


Thank you for all your help.


Douglas J Steele said:
gstrDirView looks syntactically correct to me, as does the DoCmd.OpenForm
statement. You say you've "gotten rid of the syntax error and the correct
records are returning if the form is loaded". What exactly is the problem
when the form isn't loaded?

My comment about the recordset is that you're opening the recordset rst, but
not doing anything with it. Since rst is declared inside the procedure, it's
not available anywhere else in the form, and opening a recordset has no
impact on opening a form. My recommendation is to remove all references to
db and rst in your procedure, since all it's accomplishing is taking
processing time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
I am sorry - I attempted to answer both questions in one of my many responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND [DirID] = 5

The reason the form is not open is because my users like to use the menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering crieria and
then opening the results in a form or a report.

Thank you


Douglas J. Steele said:
You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you open the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate & "#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " & "#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


:

To see what the value of gstrDirView, you can put a break-point in the
code
just before you open the form and then hold your mouse pointer over the
variable to see its value, or you can put a Debug.Print statement in the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am a begginer user of Access. I am trying to learn this tool. The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating one of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I
work
like to use the switchboard and do no tlike their desktops all clutered
up.


I forget how ato the value fo gstrDirView - I do not have my reference
book
here - but I remember it being easy. (The "Filter" on the Properties
Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


:

AFAIK, there is no IsNothing function in Access, so I'm suspecting
it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form
isn't
already opened. The recordset you're opening has nothing to do with
the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
wrote
in
message I am attempting to set up a screen from which users can select
records
using
"AND" logic. I am coming up with either "Syntax" or "Type
Mismatch"
errors
and I can't understand why. The cose so far is shown below. The
Selection
form has 3 criteria slection posibilities.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

Not that it makes any sense (given that you say it works if the form's
already open), but what data type is DirID? The sample string you gave for
gstrDirView will only work if it's a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
Users will be selecting thier criteria in a form called "FDialogSelDir".
After they have made thier selection I want to open the form "FDirView".
when "FDirView" is open at the time they make thier selection the appropriate
records show on "FDirView". However if "FDirView" is opened based on the
DoCmd.OpenForm I get a "Type Mismatch" error.


Thank you for all your help.


Douglas J Steele said:
gstrDirView looks syntactically correct to me, as does the DoCmd.OpenForm
statement. You say you've "gotten rid of the syntax error and the correct
records are returning if the form is loaded". What exactly is the problem
when the form isn't loaded?

My comment about the recordset is that you're opening the recordset rst, but
not doing anything with it. Since rst is declared inside the procedure, it's
not available anywhere else in the form, and opening a recordset has no
impact on opening a form. My recommendation is to remove all references to
db and rst in your procedure, since all it's accomplishing is taking
processing time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message news:[email protected]...
I am sorry - I attempted to answer both questions in one of my many responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND
Click to expand...
[DirID] =
5
The reason the form is not open is because my users like to use the menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering
Click to expand...
crieria
and
then opening the results in a form or a report.

Thank you


:

You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you
Click to expand...
open
the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Ralph Wischnewski" <[email protected]>
Click to expand...
wrote
in
message I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error
Click to expand...
and
the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" &
Click to expand...
Me!FromDate &
"#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= "
Click to expand...
&
"#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " & Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


:

To see what the value of gstrDirView, you can put a break-point
Click to expand...
in
the
code
just before you open the form and then hold your mouse pointer
Click to expand...
over
the
variable to see its value, or you can put a Debug.Print statement
Click to expand...
in
the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am a begginer user of Access. I am trying to learn this
Click to expand...
tool.
The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating
Click to expand...
one
of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users where I
work
like to use the switchboard and do no tlike their desktops all clutered
up.


I forget how ato the value fo gstrDirView - I do not have my reference
book
here - but I remember it being easy. (The "Filter" on the Properties
Data
tab is showing blank on the form being opened).

Will this allow you to help?

Thank you


:

AFAIK, there is no IsNothing function in Access, so I'm suspecting
it's
something you wrote yourself. What's the code for it?

I don't understand the point of opening the recordset if the form
isn't
already opened. The recordset you're opening has nothing to
Click to expand...
do
with
the
form.

What's the actual value of gstrDirView when the errors arise?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski"
Click to expand...
wrote
in
message I am attempting to set up a screen from which users can select
records
using
"AND" logic. I am coming up with either "Syntax" or "Type
Mismatch"
errors
and I can't understand why. The cose so far is shown
Click to expand...
below.
The
Selection
form has 3 criteria slection posibilities.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

DirId is a numeric field.

To avoid this "Non-sensical" issue, I am going to make the FDirView
invisible to start and then make it visible once the criteria are selected.
(hopefully I can do this).

I appreciate your help

Douglas J Steele said:
Not that it makes any sense (given that you say it works if the form's
already open), but what data type is DirID? The sample string you gave for
gstrDirView will only work if it's a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
Users will be selecting thier criteria in a form called "FDialogSelDir".
After they have made thier selection I want to open the form "FDirView".
when "FDirView" is open at the time they make thier selection the appropriate
records show on "FDirView". However if "FDirView" is opened based on the
DoCmd.OpenForm I get a "Type Mismatch" error.


Thank you for all your help.


Douglas J Steele said:
gstrDirView looks syntactically correct to me, as does the DoCmd.OpenForm
statement. You say you've "gotten rid of the syntax error and the correct
records are returning if the form is loaded". What exactly is the problem
when the form isn't loaded?

My comment about the recordset is that you're opening the recordset rst, but
not doing anything with it. Since rst is declared inside the procedure, it's
not available anywhere else in the form, and opening a recordset has no
impact on opening a form. My recommendation is to remove all references to
db and rst in your procedure, since all it's accomplishing is taking
processing time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am sorry - I attempted to answer both questions in one of my many
responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND [DirID] =
5

The reason the form is not open is because my users like to use the menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering crieria
and
then opening the results in a form or a report.

Thank you


:

You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you open
the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


in
message I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and
the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate &
"#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " &
"#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA
Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


:

To see what the value of gstrDirView, you can put a break-point in
the
code
just before you open the form and then hold your mouse pointer over
the
variable to see its value, or you can put a Debug.Print statement in
the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
wrote in
message I am a begginer user of Access. I am trying to learn this tool.
The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating one
of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble,
vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users
where I
work
like to use the switchboard and do no tlike their desktops all
clutered
up.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

DirId is numeric.

To avoid this "non-sensical" issue, I am going to make the FDirView form
invisible until the criteria are selected (I hope I can do something like
this)

I appreciate your help.

Douglas J Steele said:
Not that it makes any sense (given that you say it works if the form's
already open), but what data type is DirID? The sample string you gave for
gstrDirView will only work if it's a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
Users will be selecting thier criteria in a form called "FDialogSelDir".
After they have made thier selection I want to open the form "FDirView".
when "FDirView" is open at the time they make thier selection the appropriate
records show on "FDirView". However if "FDirView" is opened based on the
DoCmd.OpenForm I get a "Type Mismatch" error.


Thank you for all your help.


Douglas J Steele said:
gstrDirView looks syntactically correct to me, as does the DoCmd.OpenForm
statement. You say you've "gotten rid of the syntax error and the correct
records are returning if the form is loaded". What exactly is the problem
when the form isn't loaded?

My comment about the recordset is that you're opening the recordset rst, but
not doing anything with it. Since rst is declared inside the procedure, it's
not available anywhere else in the form, and opening a recordset has no
impact on opening a form. My recommendation is to remove all references to
db and rst in your procedure, since all it's accomplishing is taking
processing time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message I am sorry - I attempted to answer both questions in one of my many
responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND [DirID] =
5

The reason the form is not open is because my users like to use the menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering crieria
and
then opening the results in a form or a report.

Thank you


:

You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before you open
the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


in
message I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax error and
the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] =" & "'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate &
"#"
Else
gstrDirView = gstrDirView & " AND [TargetDate] >= " &
"#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA
Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


:

To see what the value of gstrDirView, you can put a break-point in
the
code
just before you open the form and then hold your mouse pointer over
the
variable to see its value, or you can put a Debug.Print statement in
the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
wrote in
message I am a begginer user of Access. I am trying to learn this tool.
The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also creating one
of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble,
vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users
where I
work
like to use the switchboard and do no tlike their desktops all
clutered
up.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

Just to clarify, my comment about "not that it makes any sense" was in
reference to the fact that it doesn't make sense to worry about the data
type of DirID since the string works properly in one case, but not the
other.

Good luck!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ralph Wischnewski said:
DirId is a numeric field.

To avoid this "Non-sensical" issue, I am going to make the FDirView
invisible to start and then make it visible once the criteria are selected.
(hopefully I can do this).

I appreciate your help

Douglas J Steele said:
Not that it makes any sense (given that you say it works if the form's
already open), but what data type is DirID? The sample string you gave for
gstrDirView will only work if it's a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message news:[email protected]...
Users will be selecting thier criteria in a form called "FDialogSelDir".
After they have made thier selection I want to open the form "FDirView".
when "FDirView" is open at the time they make thier selection the appropriate
records show on "FDirView". However if "FDirView" is opened based on the
DoCmd.OpenForm I get a "Type Mismatch" error.


Thank you for all your help.


:

gstrDirView looks syntactically correct to me, as does the DoCmd.OpenForm
statement. You say you've "gotten rid of the syntax error and the correct
records are returning if the form is loaded". What exactly is the problem
when the form isn't loaded?

My comment about the recordset is that you're opening the recordset
Click to expand...
rst,
but
not doing anything with it. Since rst is declared inside the
Click to expand...
procedure,
it's
not available anywhere else in the form, and opening a recordset has no
impact on opening a form. My recommendation is to remove all
Click to expand...
references
to
db and rst in your procedure, since all it's accomplishing is taking
processing time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski" <[email protected]>
Click to expand...
wrote
in
message I am sorry - I attempted to answer both questions in one of my many
responses.

The gstrDirView will for example return:

[LocCust] ='C1685-001 226' AND [TargetDate] >= #01/01/2006# AND [DirID] =
5

The reason the form is not open is because my users like to use
Click to expand...
the
menus
provided by the Access Switchboard, and they do not like multiple froms on
thier desktops, so I usually go through the process of entering crieria
and
then opening the results in a form or a report.

Thank you


:

You still haven't told me what value you're getting in gstrDirView.

(Nor have you told me why you're opening that recordset before
Click to expand...
you
open
the
form!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


in
message I think I have isolated the problem to the situation where the form
"FDirView" is not loaded. I have gotten rid of the syntax
Click to expand...
error
and
the
correct records are returning if the form is loaded.

Any further ideas how to deal with this?

My code now looks like:

Private Sub CmdView_Click()
On Error GoTo Err_CmdView_Click


Dim db As Database
Dim rst As Recordset


'Clear the global Directive filter string

gstrDirView = ""

' and parse out a new one


'*****Set Location ID -------Syntax error

If Not IsNothing(Me!CboLoc) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[LocCust] =" & "'" & Me!CboLoc & "'"
Else
gstrDirView = gstrDirView & " AND [LocCust] ="
Click to expand...
&
"'" &
Me!CboLoc & "'"
End If
End If

'********Set Date Comparison -----Type Mismatch
If Not IsNothing(Me!FromDate) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[TargetDate] >= " & "#" & Me!FromDate &
"#"
Else
gstrDirView = gstrDirView & " AND [TargetDate]
Click to expand...
= "
&
"#" &
Me!FromDate & "#"
End If
End If

'*********Set DirID--------Type Mismatch
If Not IsNothing(Me!CboDirID) Then
If IsNothing(gstrDirView) Then
gstrDirView = "[DirID] = " & Me!CboDirID
Else
gstrDirView = gstrDirView & " AND [DirID] = " &
Me.CboDirID
End If
End If





'*********If No criteria, then nothing to do

If IsNothing(gstrDirView) Then
MsgBox "No Criteria Specified.", vbExclamation, "TSSA
Directives"
Exit Sub
End If


'********Search based on the string


Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

Set db = CurrentDb
Set rst = db.OpenRecordset( _
"SELECT DISTINCTROW " & _
"QDirView.DirID " & _
"FROM QDirView " & _
"WHERE " & gstrDirView & ";")





DoCmd.OpenForm FormName:="FDirView", _
WhereCondition:=gstrDirView


Exit Sub

End If

Exit_CmdView_Click:
Exit Sub

Err_CmdView_Click:
MsgBox Err.Description
Resume Exit_CmdView_Click

End Sub


:

To see what the value of gstrDirView, you can put a
Click to expand...
break-point
in
the
code
just before you open the form and then hold your mouse
Click to expand...
pointer
over
the
variable to see its value, or you can put a Debug.Print
Click to expand...
statement
in
the
code and look in the Immediate window (Ctrl-G) to see what's printed:

Debug.Print gstrDirView

If IsLoaded("FDirView") Then

Forms!FDirView.SetFocus
DoCmd.ApplyFilter , gstrDirView

Else

DoCmd.OpenForm "FDirView", acPreview, _
WhereCondition:=gstrDirView

Exit Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Ralph Wischnewski"
Click to expand...
wrote in
message I am a begginer user of Access. I am trying to learn this tool.
The
IsNothing function is someting I found in my research along the
learning
curve - from the Acess book by J Viscas(?). I used this in another
little
application I created where I was successful in also
Click to expand...
creating
one
of
these
selection screens.
The code is as follows:

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble,
vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then
IsNothing =
False
End Select

End Function


The form is'nt already open because I have found that the users
where I
work
like to use the switchboard and do no tlike their desktops all
clutered
up.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Global Filter to a Report 1
Sorting Records 1
Dlookup problem in coding 2
searching and finding records from separate tables 1
Type Mismatch when pulling value from Combo 1
search coding 2
Unpredicatable BeforeUpdate event 1
Deleting a single field of a single record 3

Top