SQL Form Field Date Criteria Reference

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have a problem with the criteria reference on a date
field.

This query is being used to locate a record based on
three fields in a form. One of those fields is a date
field. I can't get the date field criteria properly
referenced. Any helo would be appreciated!

SELECT DISTINCT Activity.SlsRepID, Activity.Date,
Activity.Time FROM Activity WHERE (((Activity.SlsRepID)="
& [Forms]![Schedule Data Entry]![SalesRep] & ") AND
((Activity.Date)=# & [Forms]![Schedule Data Entry]!
[cboDate] & #) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));

The time reference is actually a text field refering to
AM, PM or All day.

Thanks!

Kevin
 
My suggestion for your query is to declare some variables for the date and
time and SlsRepID.
For example
Dim SalesRepID As String
Dim ActivityDate As date
Dim ActivityTime As Date
Dim strSQl as String

Your query would then be simplified by doing this
SalesRepID = [Forms]![Schedule Data Entry]![SalesRep]
ActivityDate = [Forms]![Schedule Data Entry]![cboDate]
ActivityTime = [Forms]! [Schedule Data Entry]![Time]

'Comment Your SQL Query string will be
strSQL = "SELECT DISTINCT Activity.SlsRepID, Activity.Date"
strSQL = strSQL & " Activity.Time FROM Activity WHERE"
strSQL = strSQL & " Activity.SlsRepID)= SalesRepID"
strSQL = strSQL & " AND Activity.Date= ActivityDate"
strSQL = strSQL & " AND Activity.Time)= ActivityTime

qryDef.SQL = strSQL
db.Execute "qry XXXXXXXX"

This is a snipit of code from my database. You may need additional
clarification for the specifics. Please post another reply giving more
details about what you are trying to accomplish and I will try to help. If
you use a datePicker control to provide the date the time is also included.
Is the time for the Sales Representative that important? Is this a query to
find a sales transaction for a sales person for a specific sales transaction
time during a specific day? How are you capturing the date and time of this
sales transaction? What is the data type for the date and time fields in
your database?
Answers to these questions will help me focus an a specific solution
example.

Cheers,
Henry
 
SELECT ... WHERE ... MyDate = #1/2/3004# ...

where the date value must be specified in month/day/year order, *regardless
of your PC settings*.

HTH,
TC


Kevin said:
Henry,

Thanks for the response. The time is not a standard time
value, but AM, PM, Allday and yes it's important.

When I place a where condition in my SQL statemnt the way
you have yours show, I get an error. It is a syntax
error. What I need to know is in VBA code, how to wrap
the date in the where clause of the sql statement.

For example, a string variable is wrapped with
'" & MyStringVariable & "'. A number is wrapped with
" & MyIntegerVAriable & ". How do you treate a date. I
can assign the variables, as suggested. That is a good
suggestion!

Thanks again for the help!

Kevin
-----Original Message-----
My suggestion for your query is to declare some variables for the date and
time and SlsRepID.
For example
Dim SalesRepID As String
Dim ActivityDate As date
Dim ActivityTime As Date
Dim strSQl as String

Your query would then be simplified by doing this
SalesRepID = [Forms]![Schedule Data Entry]![SalesRep]
ActivityDate = [Forms]![Schedule Data Entry]![cboDate]
ActivityTime = [Forms]! [Schedule Data Entry]![Time]

'Comment Your SQL Query string will be
strSQL = "SELECT DISTINCT Activity.SlsRepID, Activity.Date"
strSQL = strSQL & " Activity.Time FROM Activity WHERE"
strSQL = strSQL & " Activity.SlsRepID)= SalesRepID"
strSQL = strSQL & " AND Activity.Date= ActivityDate"
strSQL = strSQL & " AND Activity.Time)= ActivityTime

qryDef.SQL = strSQL
db.Execute "qry XXXXXXXX"

This is a snipit of code from my database. You may need additional
clarification for the specifics. Please post another reply giving more
details about what you are trying to accomplish and I will try to help. If
you use a datePicker control to provide the date the time is also included.
Is the time for the Sales Representative that important? Is this a query to
find a sales transaction for a sales person for a specific sales transaction
time during a specific day? How are you capturing the date and time of this
sales transaction? What is the data type for the date and time fields in
your database?
Answers to these questions will help me focus an a specific solution
example.

Cheers,
Henry

I have a problem with the criteria reference on a date
field.

This query is being used to locate a record based on
three fields in a form. One of those fields is a date
field. I can't get the date field criteria properly
referenced. Any helo would be appreciated!

SELECT DISTINCT Activity.SlsRepID, Activity.Date,
Activity.Time FROM Activity WHERE (((Activity.SlsRepID) ="
& [Forms]![Schedule Data Entry]![SalesRep] & ") AND
((Activity.Date)=# & [Forms]![Schedule Data Entry]!
[cboDate] & #) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));

The time reference is actually a text field refering to
AM, PM or All day.

Thanks!

Kevin
Click to expand...


.
Click to expand...
Click to expand...
 
Henry,

Thanks for the response. The time is not a standard time
value, but AM, PM, Allday and yes it's important.

When I place a where condition in my SQL statemnt the way
you have yours show, I get an error. It is a syntax
error. What I need to know is in VBA code, how to wrap
the date in the where clause of the sql statement.

For example, a string variable is wrapped with
'" & MyStringVariable & "'. A number is wrapped with
" & MyIntegerVAriable & ". How do you treate a date. I
can assign the variables, as suggested. That is a good
suggestion!

Thanks again for the help!

Kevin
-----Original Message-----
My suggestion for your query is to declare some variables for the date and
time and SlsRepID.
For example
Dim SalesRepID As String
Dim ActivityDate As date
Dim ActivityTime As Date
Dim strSQl as String

Your query would then be simplified by doing this
SalesRepID = [Forms]![Schedule Data Entry]![SalesRep]
ActivityDate = [Forms]![Schedule Data Entry]![cboDate]
ActivityTime = [Forms]! [Schedule Data Entry]![Time]

'Comment Your SQL Query string will be
strSQL = "SELECT DISTINCT Activity.SlsRepID, Activity.Date"
strSQL = strSQL & " Activity.Time FROM Activity WHERE"
strSQL = strSQL & " Activity.SlsRepID)= SalesRepID"
strSQL = strSQL & " AND Activity.Date= ActivityDate"
strSQL = strSQL & " AND Activity.Time)= ActivityTime

qryDef.SQL = strSQL
db.Execute "qry XXXXXXXX"

This is a snipit of code from my database. You may need additional
clarification for the specifics. Please post another reply giving more
details about what you are trying to accomplish and I will try to help. If
you use a datePicker control to provide the date the time is also included.
Is the time for the Sales Representative that important? Is this a query to
find a sales transaction for a sales person for a specific sales transaction
time during a specific day? How are you capturing the date and time of this
sales transaction? What is the data type for the date and time fields in
your database?
Answers to these questions will help me focus an a specific solution
example.

Cheers,
Henry

I have a problem with the criteria reference on a date
field.

This query is being used to locate a record based on
three fields in a form. One of those fields is a date
field. I can't get the date field criteria properly
referenced. Any helo would be appreciated!

SELECT DISTINCT Activity.SlsRepID, Activity.Date,
Activity.Time FROM Activity WHERE (((Activity.SlsRepID) ="
& [Forms]![Schedule Data Entry]![SalesRep] & ") AND
((Activity.Date)=# & [Forms]![Schedule Data Entry]!
[cboDate] & #) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));

The time reference is actually a text field refering to
AM, PM or All day.

Thanks!

Kevin
Click to expand...


.
Click to expand...
 
Kevin,
Sorry I goofed in my example code. It should have read:

***begin code example****
Dim db as DAO.Database
Dim Rs as DAO.Recordset
Dim SalesRepID As String
Dim ActivityDate As date
Dim ActivityTime As Date
Dim strSQL as String

Your query would then be simplified by doing this
SalesRepID = [Forms]![Schedule Data Entry]![SalesRep]
ActivityDate = [Forms]![Schedule Data Entry]![cboDate]
ActivityTime = [Forms]! [Schedule Data Entry]![Time]

Set db = Access.CurrentDb
Set qryDef = db.CreateQueryDef ("qryXXXXXX")

'Comment Your SQL Query string will be
strSQL = "SELECT DISTINCT Activity.SlsRepID, Activity.Date"
strSQL = strSQL & " Activity.Time FROM Activity WHERE"
strSQL = strSQL & " Activity.SlsRepID = " & SalesRepID
strSQL = strSQL & " AND Activity.Date = " & ActivityDate
strSQL = strSQL & " AND Activity.Time = " & ActivityTime

qryDef.SQL = strSQL
Set Rs = db.OpenRecordset(strSQL)
****end of example procedure************
This will provide you with a record set containing the records that match
the criteria of a specified date and time.
If your data and time are not Access standard times and dates then you will
have to perform some data manipulation with your variables before you use
them in a SQL query.
Please provide a few examples of the contents of the date and time fields in
your table and I can help you some more.

Cheers,
Henry
Kevin said:
Henry,

Thanks for the response. The time is not a standard time
value, but AM, PM, Allday and yes it's important.

When I place a where condition in my SQL statemnt the way
you have yours show, I get an error. It is a syntax
error. What I need to know is in VBA code, how to wrap
the date in the where clause of the sql statement.

For example, a string variable is wrapped with
'" & MyStringVariable & "'. A number is wrapped with
" & MyIntegerVAriable & ". How do you treate a date. I
can assign the variables, as suggested. That is a good
suggestion!

Thanks again for the help!

Kevin
-----Original Message-----
My suggestion for your query is to declare some variables for the date and
time and SlsRepID.
For example
Dim SalesRepID As String
Dim ActivityDate As date
Dim ActivityTime As Date
Dim strSQl as String

Your query would then be simplified by doing this
SalesRepID = [Forms]![Schedule Data Entry]![SalesRep]
ActivityDate = [Forms]![Schedule Data Entry]![cboDate]
ActivityTime = [Forms]! [Schedule Data Entry]![Time]

'Comment Your SQL Query string will be
strSQL = "SELECT DISTINCT Activity.SlsRepID, Activity.Date"
strSQL = strSQL & " Activity.Time FROM Activity WHERE"
strSQL = strSQL & " Activity.SlsRepID)= SalesRepID"
strSQL = strSQL & " AND Activity.Date= ActivityDate"
strSQL = strSQL & " AND Activity.Time)= ActivityTime

qryDef.SQL = strSQL
db.Execute "qry XXXXXXXX"

This is a snipit of code from my database. You may need additional
clarification for the specifics. Please post another reply giving more
details about what you are trying to accomplish and I will try to help. If
you use a datePicker control to provide the date the time is also included.
Is the time for the Sales Representative that important? Is this a query to
find a sales transaction for a sales person for a specific sales transaction
time during a specific day? How are you capturing the date and time of this
sales transaction? What is the data type for the date and time fields in
your database?
Answers to these questions will help me focus an a specific solution
example.

Cheers,
Henry

I have a problem with the criteria reference on a date
field.

This query is being used to locate a record based on
three fields in a form. One of those fields is a date
field. I can't get the date field criteria properly
referenced. Any helo would be appreciated!

SELECT DISTINCT Activity.SlsRepID, Activity.Date,
Activity.Time FROM Activity WHERE (((Activity.SlsRepID) ="
& [Forms]![Schedule Data Entry]![SalesRep] & ") AND
((Activity.Date)=# & [Forms]![Schedule Data Entry]!
[cboDate] & #) AND ((Activity.Time)='" & [Forms]!
[Schedule Data Entry]![Time] & "'));

The time reference is actually a text field refering to
AM, PM or All day.

Thanks!

Kevin
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top