DATE SEARCH CBO

J

jerseygirl54

I would like to be able to search classes by date. I have created a query
ClassesSearch Query and then created a form with an unbound combo box that
points to Row Source. When I click on the ... I have, ClassID [Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you entered
isn't an item on the list. Select an item from the list, or enter text that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression." and it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
D

Douglas J. Steele

Dates need to be delimited with # (and must be in a format Access will treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")
 
J

jerseygirl54

I changed it but still get an error. Valid date is not recognized and when I
click the button I get "Compile error: Syntax error."

It points to the line that was changed (see a copy of what I changed below
this is in red)
strCriteria = "[Class Date] = "&_Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

and this line is in Yellow:

Private Sub Command10_Click()




Douglas J. Steele said:
Dates need to be delimited with # (and must be in a format Access will treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


jerseygirl54 said:
I would like to be able to search classes by date. I have created a query
ClassesSearch Query and then created a form with an unbound combo box that
points to Row Source. When I click on the ... I have, ClassID [Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you
entered
isn't an item on the list. Select an item from the list, or enter text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression." and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
D

Douglas J. Steele

The underscore is a line continuation character. I tend to use them in
newsgroup posts to avoid issues with line wrap.

If you're putting it all on one line, remove the underscore.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jerseygirl54 said:
I changed it but still get an error. Valid date is not recognized and when
I
click the button I get "Compile error: Syntax error."

It points to the line that was changed (see a copy of what I changed below
this is in red)
strCriteria = "[Class Date] = "&_Format(Me.[cboDateQuery],
"\#yyyy\-mm\-dd\#")

and this line is in Yellow:

Private Sub Command10_Click()




Douglas J. Steele said:
Dates need to be delimited with # (and must be in a format Access will
treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


jerseygirl54 said:
I would like to be able to search classes by date. I have created a
query
ClassesSearch Query and then created a form with an unbound combo box
that
points to Row Source. When I click on the ... I have, ClassID
[Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you
entered
isn't an item on the list. Select an item from the list, or enter text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression."
and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
J

jerseygirl54

Removed the underscore. Still will not recognize a valid date and ten when I
pick a valid class from the list it pulls up a blank class form.

Douglas J. Steele said:
The underscore is a line continuation character. I tend to use them in
newsgroup posts to avoid issues with line wrap.

If you're putting it all on one line, remove the underscore.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jerseygirl54 said:
I changed it but still get an error. Valid date is not recognized and when
I
click the button I get "Compile error: Syntax error."

It points to the line that was changed (see a copy of what I changed below
this is in red)
strCriteria = "[Class Date] = "&_Format(Me.[cboDateQuery],
"\#yyyy\-mm\-dd\#")

and this line is in Yellow:

Private Sub Command10_Click()




Douglas J. Steele said:
Dates need to be delimited with # (and must be in a format Access will
treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I would like to be able to search classes by date. I have created a
query
ClassesSearch Query and then created a form with an unbound combo box
that
points to Row Source. When I click on the ... I have, ClassID
[Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you
entered
isn't an item on the list. Select an item from the list, or enter text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression."
and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
D

Douglas J. Steele

Immediately before the line of code

DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

put

Debug.Print strCriteria

Once the code runs (and fails), go to the Immediate Window (Ctrl-G) and
check what's been printed there. Post it back in this thread.

Although when you say "will not recognize a valid date and ten", do you mean
date and time? If so, you'll need to change the format to

Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd hh:\nn\:ss\#")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jerseygirl54 said:
Removed the underscore. Still will not recognize a valid date and ten
when I
pick a valid class from the list it pulls up a blank class form.

Douglas J. Steele said:
The underscore is a line continuation character. I tend to use them in
newsgroup posts to avoid issues with line wrap.

If you're putting it all on one line, remove the underscore.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jerseygirl54 said:
I changed it but still get an error. Valid date is not recognized and
when
I
click the button I get "Compile error: Syntax error."

It points to the line that was changed (see a copy of what I changed
below
this is in red)
strCriteria = "[Class Date] = "&_Format(Me.[cboDateQuery],
"\#yyyy\-mm\-dd\#")

and this line is in Yellow:

Private Sub Command10_Click()




:

Dates need to be delimited with # (and must be in a format Access will
treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I would like to be able to search classes by date. I have created a
query
ClassesSearch Query and then created a form with an unbound combo
box
that
points to Row Source. When I click on the ... I have, ClassID
[Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the
event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid
Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you
entered
isn't an item on the list. Select an item from the list, or enter
text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression."
and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
J

jerseygirl54

In answer to your question if I mean time when you read ten, no just mistyped
and should have read "then".

I did put the "Debug.Print" line, but I don't get any error any more. I
just get a blank Class form no matter which class I pick. Still does not
recognize a valid date.

Douglas J. Steele said:
Immediately before the line of code

DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

put

Debug.Print strCriteria

Once the code runs (and fails), go to the Immediate Window (Ctrl-G) and
check what's been printed there. Post it back in this thread.

Although when you say "will not recognize a valid date and ten", do you mean
date and time? If so, you'll need to change the format to

Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd hh:\nn\:ss\#")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jerseygirl54 said:
Removed the underscore. Still will not recognize a valid date and ten
when I
pick a valid class from the list it pulls up a blank class form.

Douglas J. Steele said:
The underscore is a line continuation character. I tend to use them in
newsgroup posts to avoid issues with line wrap.

If you're putting it all on one line, remove the underscore.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed it but still get an error. Valid date is not recognized and
when
I
click the button I get "Compile error: Syntax error."

It points to the line that was changed (see a copy of what I changed
below
this is in red)
strCriteria = "[Class Date] = "&_Format(Me.[cboDateQuery],
"\#yyyy\-mm\-dd\#")

and this line is in Yellow:

Private Sub Command10_Click()




:

Dates need to be delimited with # (and must be in a format Access will
treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I would like to be able to search classes by date. I have created a
query
ClassesSearch Query and then created a form with an unbound combo
box
that
points to Row Source. When I click on the ... I have, ClassID
[Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the
event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid
Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you
entered
isn't an item on the list. Select an item from the list, or enter
text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression."
and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
J

jerseygirl54

I tried this a couple of times, but I guess I'm missing something. When I
place the combo box in the header, I don't see it.

Steve said:
Create a query named QryClassDate based on your table that holds the class
data. Add only one field to your query - Class Date. Right click in a blank
area up in the window where your table is at and click on Properties.
Change the Unique Values property to Yes. Close Properties and close the
design view of the query.

Your form needs to be a continuous form. Open the form in design view. The
unbound combo box needs to be in the Header section. Select the combobox and
open Properties. Under the Data tab, make QryClassDate the Row Source of the
combobox. Set the bound column to 1. Go to the Format tab and set Column
Count to 1 and Column Width to 1". Go to the Other tab and make the Name
property ClassDate. Select form at the top of Properties, go to the Other
tab and make the Name property of your form FrmClassesSearch.

Put the following expression in the criteria of Class Date in your
ClassesSearch Query:
Forms!FrmClassesSearch!ClassDate.

One last thing ....
Put the following code in the AfterUpdate event of your combobox:
Me.Requery

Steve


jerseygirl54 said:
I would like to be able to search classes by date. I have created a query
ClassesSearch Query and then created a form with an unbound combo box that
points to Row Source. When I click on the ... I have, ClassID [Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid Operation"
End If


End Sub

First error - I get an error message on a valid date "The text you
entered
isn't an item on the list. Select an item from the list, or enter text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria expression." and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be greatly
appreciated! :)
 
D

Douglas J. Steele

What was printed as the content of strCriteria?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jerseygirl54 said:
In answer to your question if I mean time when you read ten, no just
mistyped
and should have read "then".

I did put the "Debug.Print" line, but I don't get any error any more. I
just get a blank Class form no matter which class I pick. Still does not
recognize a valid date.

Douglas J. Steele said:
Immediately before the line of code

DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

put

Debug.Print strCriteria

Once the code runs (and fails), go to the Immediate Window (Ctrl-G) and
check what's been printed there. Post it back in this thread.

Although when you say "will not recognize a valid date and ten", do you
mean
date and time? If so, you'll need to change the format to

Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd hh:\nn\:ss\#")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jerseygirl54 said:
Removed the underscore. Still will not recognize a valid date and ten
when I
pick a valid class from the list it pulls up a blank class form.

:

The underscore is a line continuation character. I tend to use them in
newsgroup posts to avoid issues with line wrap.

If you're putting it all on one line, remove the underscore.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I changed it but still get an error. Valid date is not recognized
and
when
I
click the button I get "Compile error: Syntax error."

It points to the line that was changed (see a copy of what I changed
below
this is in red)
strCriteria = "[Class Date] = "&_Format(Me.[cboDateQuery],
"\#yyyy\-mm\-dd\#")

and this line is in Yellow:

Private Sub Command10_Click()




:

Dates need to be delimited with # (and must be in a format Access
will
treat
correctly)

strCriteria = "[Class Date] = " & _
Format(Me.[cboDateQuery], "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I would like to be able to search classes by date. I have created
a
query
ClassesSearch Query and then created a form with an unbound combo
box
that
points to Row Source. When I click on the ... I have, ClassID
[Classes],
ClassName [Classes], and Class Date [Classes] Sort Ascending.

I then created a button on the form and placed this code on the
event:

Private Sub Command10_Click()
Dim strCriteria As String

If Not IsNull(Me.cboDateQuery) Then
'build criterion for search
strCriteria = "[Class Date] = """ & Me.[cboDateQuery] & """"

'open form filtered to selected class
DoCmd.OpenForm "Classes", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name

Else
'otherwise inform user
MsgBox "Class date not valid.", vbInformation, "Invalid
Operation"
End If


End Sub

First error - I get an error message on a valid date "The text
you
entered
isn't an item on the list. Select an item from the list, or
enter
text
that
matches on of the listed items."

I select a class from the list then I get another error:

"Run-time error '3464': Data type mismatch in criteria
expression."
and
it
points to the DoCmd.OpenForm "Classes"...line.

If someone could show me what I'm doing wrong, it would be
greatly
appreciated! :)
 
J

John W. Vinson/MVP

I tried this a couple of times, but I guess I'm missing something. When I
place the combo box in the header, I don't see it.

Is the Default View of the subform Continuous Forms (good) or
Datasheet (bad)? Continuous forms have headers, datasheets don't.
 
J

jerseygirl54

I just created a "form". Sorry, I don't know what subformcontinous form is.
:-( Could you walk me through, please?
 
J

jerseygirl54

I created the continuous form with the unbound combo box at the header. It's
still not correct because all I get is the QryClassDate with a date shown
when I enter a date. I would like the query to open the specific class form
for that date. HeLP Please! :)
 

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

Top