Problem with query criteria

C

Chuck216

Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.
= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )
Click to expand...


Thanks in advance for any help with this.
Chuck
 
K

KARL DEWEY

I think your problem is the LIKE function. With it you need a wild card.
Access stores a date and a decimal number.

If the number were 739.2371 how would you expect to apply a wild card to a
criteria for the number? You can not as your input such as 3/5/2009 is being
translated to the decimal number.

What do you want to enter by way of the form and what do you want returned?
--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.
= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )
Click to expand...


Thanks in advance for any help with this.
Chuck
Click to expand...
 
C

Chuck216

if I don't put Like I get the error The expression is
typed incorrectly or is too complex to be evaluated

what I want returned is all records from Jan thru June.




KARL DEWEY said:
I think your problem is the LIKE function. With it you need a wild card.
Access stores a date and a decimal number.

If the number were 739.2371 how would you expect to apply a wild card to a
criteria for the number? You can not as your input such as 3/5/2009 is being
translated to the decimal number.

What do you want to enter by way of the form and what do you want returned?
--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.
= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )
Click to expand...


Thanks in advance for any help with this.
Chuck
Click to expand...
Click to expand...
 
K

KARL DEWEY

What are you entering in [Forms]![frmAviatorSemiAnnualMenu]![Text14] to try
and get Jan thru June?

--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
if I don't put Like I get the error The expression is
typed incorrectly or is too complex to be evaluated

what I want returned is all records from Jan thru June.




KARL DEWEY said:
I think your problem is the LIKE function. With it you need a wild card.
Access stores a date and a decimal number.

If the number were 739.2371 how would you expect to apply a wild card to a
criteria for the number? You can not as your input such as 3/5/2009 is being
translated to the decimal number.

What do you want to enter by way of the form and what do you want returned?
--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.

= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )


Thanks in advance for any help with this.
Chuck
Click to expand...
Click to expand...
Click to expand...
 
C

Chuck216

= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )


KARL DEWEY said:
What are you entering in [Forms]![frmAviatorSemiAnnualMenu]![Text14] to try
and get Jan thru June?

--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
if I don't put Like I get the error The expression is
typed incorrectly or is too complex to be evaluated

what I want returned is all records from Jan thru June.




KARL DEWEY said:
I think your problem is the LIKE function. With it you need a wild card.
Access stores a date and a decimal number.

If the number were 739.2371 how would you expect to apply a wild card to a
criteria for the number? You can not as your input such as 3/5/2009 is being
translated to the decimal number.

What do you want to enter by way of the form and what do you want returned?
--
KARL DEWEY
Build a little - Test a little


:

Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.

= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )


Thanks in advance for any help with this.
Chuck
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I see the problem now. What you are entering in the text box comes into the
query as a string. I is trying to compare your text string to a date that is
a number.
That is the same as comparing 'hot dogs and butter milk' to 4730.319 and
expecting a result.

Most folks use two text boxes like this ---
Between [Forms]![frmAviatorSemiAnnualMenu]![Text14] AND
[Forms]![frmAviatorSemiAnnualMenu]![Text16]

There are other ways like this -- Enter 2009-01-07 in the text box.
In the query criteria put this --
Between DateSerial(Left([Forms]![frmAviatorSemiAnnualMenu]![Text14], 4),
Right(Left([Forms]![frmAviatorSemiAnnualMenu]![Text14], 7),2),1) AND
DateSerial(Left([Forms]![frmAviatorSemiAnnualMenu]![Text14], 4),
Right([Forms]![frmAviatorSemiAnnualMenu]![Text14],2)+1,0)

This take the left 4 digits for the year, middle 2 digits for month of the
begining.
The second part uses the year and adds one to the month with day as 0 (zero)
which is the last day of previos month.

--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )
Click to expand...


KARL DEWEY said:
What are you entering in [Forms]![frmAviatorSemiAnnualMenu]![Text14] to try
and get Jan thru June?

--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
if I don't put Like I get the error The expression is
typed incorrectly or is too complex to be evaluated

what I want returned is all records from Jan thru June.




:

I think your problem is the LIKE function. With it you need a wild card.
Access stores a date and a decimal number.

If the number were 739.2371 how would you expect to apply a wild card to a
criteria for the number? You can not as your input such as 3/5/2009 is being
translated to the decimal number.

What do you want to enter by way of the form and what do you want returned?
--
KARL DEWEY
Build a little - Test a little


:

Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.

= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )


Thanks in advance for any help with this.
Chuck
Click to expand...
Click to expand...
Click to expand...
 
C

Chuck216

Thank you

what I did was set to onClick to put DateSerial(Year(Date()), 1, 1) in
the VB code in one text box and DateSerial(Year(Date()), 6, 30) in another
which put the actual dates in the text boxes and it work just fine.
The key was you figuring out that I was trying to compare your text string
to a date.
again Thank you

KARL DEWEY said:
I see the problem now. What you are entering in the text box comes into the
query as a string. I is trying to compare your text string to a date that is
a number.
That is the same as comparing 'hot dogs and butter milk' to 4730.319 and
expecting a result.

Most folks use two text boxes like this ---
Between [Forms]![frmAviatorSemiAnnualMenu]![Text14] AND
[Forms]![frmAviatorSemiAnnualMenu]![Text16]

There are other ways like this -- Enter 2009-01-07 in the text box.
In the query criteria put this --
Between DateSerial(Left([Forms]![frmAviatorSemiAnnualMenu]![Text14], 4),
Right(Left([Forms]![frmAviatorSemiAnnualMenu]![Text14], 7),2),1) AND
DateSerial(Left([Forms]![frmAviatorSemiAnnualMenu]![Text14], 4),
Right([Forms]![frmAviatorSemiAnnualMenu]![Text14],2)+1,0)

This take the left 4 digits for the year, middle 2 digits for month of the
begining.
The second part uses the year and adds one to the month with day as 0 (zero)
which is the last day of previos month.

--
KARL DEWEY
Build a little - Test a little


Chuck216 said:
= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )
Click to expand...


KARL DEWEY said:
What are you entering in [Forms]![frmAviatorSemiAnnualMenu]![Text14] to try
and get Jan thru June?

--
KARL DEWEY
Build a little - Test a little


:


if I don't put Like I get the error The expression is
typed incorrectly or is too complex to be evaluated

what I want returned is all records from Jan thru June.




:

I think your problem is the LIKE function. With it you need a wild card.
Access stores a date and a decimal number.

If the number were 739.2371 how would you expect to apply a wild card to a
criteria for the number? You can not as your input such as 3/5/2009 is being
translated to the decimal number.

What do you want to enter by way of the form and what do you want returned?
--
KARL DEWEY
Build a little - Test a little


:

Hi

I have a menu form “frmAviatorSemiAnnualMenu´with a text box “Text14†and a
command button on it to open a form, the code behind the button is.

Private Sub cmdJanThruJune_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Text14 = ">= DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 7, )"
stDocName = "frmAviatorSemiAnnual"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


The form is based on a query with the following code in the criteria for
InspDate.

Like [Forms]![frmAviatorSemiAnnualMenu]![Text14]



The problem is when I click the cmd button I get no results at all. If I put
the following into the criteria of InspDate it works fine.

= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 7, )


Thanks in advance for any help with this.
Chuck
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

Datashhet View from another Form wont work 2
Pass multiple criteria with VBA 7
Query to Complex with Criteria 1
Multiple Search Command criteria in a form 2
Multiple Criteria in Where Clause 2
transfer a field using button 1
Open Form Code Error 5
Trying to get Current User info only 2

Top