Ken or Doug? -- another SQL syntax stumper

  • Thread starter Thread starter Sid
  • Start date Start date
S

Sid

Ok, I thought I had this SQL QueryDef syntax thing mastered until the date
aspect. This is a section of my test query.
(Works great with Actual times:)

....AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
...AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
....AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #" &(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past my last #.

I have tried variations of the """" like my other string control values
also...
Any suggestions appreciated
Sid
 
I think you had unbalanced parenthesis and missing quotes. Try pasting in

.... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" & Me!Sh1E & "#"

provided this line continues a properly constructed SQL string. I am
assuming this is code built inside the form module? If not, you need to
use the complete form reference and the form must be open in order for
the WHERE to work.
Pavel
 
Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E] & "#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When run,
the cursor is after the semicolon at the end. In the error display, the
Last # is not displayed....
Thanks

Pavel Romashkin said:
I think you had unbalanced parenthesis and missing quotes. Try pasting in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" & Me!Sh1E & "#"

provided this line continues a properly constructed SQL string. I am
assuming this is code built inside the form module? If not, you need to
use the complete form reference and the form must be open in order for
the WHERE to work.
Pavel
Ok, I thought I had this SQL QueryDef syntax thing mastered until the date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #" &(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past my last #.

I have tried variations of the """" like my other string control values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
 
Sid -

Post the entire section of code. It's hard to be sure of the ' and " and #
and stuff from just a snippet. Also, what is the format of the Sh1B and Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E] & "#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When run,
the cursor is after the semicolon at the end. In the error display, the
Last # is not displayed....
Thanks

Pavel Romashkin said:
I think you had unbalanced parenthesis and missing quotes. Try pasting in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" & Me!Sh1E & "#"

provided this line continues a properly constructed SQL string. I am
assuming this is code built inside the form module? If not, you need to
use the complete form reference and the form must be open in order for
the WHERE to work.
Pavel
Ok, I thought I had this SQL QueryDef syntax thing mastered until the date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #" &(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past my last #.

I have tried variations of the """" like my other string control values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
Click to expand...
 
Ken
The Sh1B and Sh1E form controls are formatted Short Date at the moment. The
Time_Off and Time_On are stored as Date/Time no format.

This is the whole section of code that doesn't work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E] & "#))
WITH OWNERACCESS OPTION;

This is the whole section that does work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate]) AND ((TimeValue([Time_On])) Between
#12/30/1899 6:0:0# And #12/30/1899 14:30:0#))
WITH OWNERACCESS OPTION;

This last example starts out with with #06:00# and #14:30# , when I save &
close it, Access changes it, it converts my times like #06:00# to
#12/30/1899 6:0:0# It does give my desired results though.
Thanks

Ken Snell said:
Sid -

Post the entire section of code. It's hard to be sure of the ' and " and #
and stuff from just a snippet. Also, what is the format of the Sh1B and Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E] & "#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When run,
the cursor is after the semicolon at the end. In the error display, the
Last # is not displayed....
Thanks

Pavel Romashkin said:
I think you had unbalanced parenthesis and missing quotes. Try pasting in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" & Me!Sh1E
Click to expand...
&
"#"
provided this line continues a properly constructed SQL string. I am
assuming this is code built inside the form module? If not, you need to
use the complete form reference and the form must be open in order for
the WHERE to work.
Pavel

Sid wrote:

Ok, I thought I had this SQL QueryDef syntax thing mastered until
Click to expand...
the
date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #" &(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past my
Click to expand...
last
#.
I have tried variations of the """" like my other string control values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
Click to expand...
 
Sid -

If I recall from our other thread, you're building an SQL in code right? So
this snippet is part of the text string that you're building?

When you say it doesn't work, are we just referring to the syntax error that
is being thrown by the compiler? Or that the query doesn't work at all? Is
the form open when you try to run the code?

Assuming that both of my comments above are correct, then you can include
the full form reference as part of the text string and you don't need the #
delimiters. This assumes that you want the query to be stored with the
ability to use those parameters, something that you may not want (as you
then will need to resolve those parameters when you run it -- thus, it would
be better to do it here when you build the query).

Post the whole section of code (the entire part of building the SQL
statement in code, etc.). It may be something subtle that isn't obvious from
what you've posted so far.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Ken
The Sh1B and Sh1E form controls are formatted Short Date at the moment. The
Time_Off and Time_On are stored as Date/Time no format.

This is the whole section of code that doesn't work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E] & "#))
WITH OWNERACCESS OPTION;

This is the whole section that does work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate]) AND ((TimeValue([Time_On])) Between
#12/30/1899 6:0:0# And #12/30/1899 14:30:0#))
WITH OWNERACCESS OPTION;

This last example starts out with with #06:00# and #14:30# , when I save &
close it, Access changes it, it converts my times like #06:00# to
#12/30/1899 6:0:0# It does give my desired results though.
Thanks

Ken Snell said:
Sid -

Post the entire section of code. It's hard to be sure of the ' and " and #
and stuff from just a snippet. Also, what is the format of the Sh1B and Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E]
Click to expand...
&
"#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When run,
the cursor is after the semicolon at the end. In the error display, the
Last # is not displayed....
Thanks

I think you had unbalanced parenthesis and missing quotes. Try
Click to expand...
pasting
in
... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" &
Click to expand...
Click to expand...
Me!Sh1E
&
"#"

provided this line continues a properly constructed SQL string. I am
assuming this is code built inside the form module? If not, you need to
use the complete form reference and the form must be open in order for
the WHERE to work.
Pavel

Sid wrote:

Ok, I thought I had this SQL QueryDef syntax thing mastered until the
date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #"
&(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past my last
#.

I have tried variations of the """" like my other string control values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
Click to expand...
 
Hey its working! Without the # and & and " signs...

My SQL section ended up like this:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
[Forms]![frmReports]![Sh1B] AND [Forms]![frmReports]![Sh1E]))
WITH OWNERACCESS OPTION;
Click to expand...

This is a stand-alone query used for my test of SQL syntax at this point.
I'll probably try to QueryDef this next.

My only remaining challenge is, my three shift times(Sh1, Sh2, Sh3) are
06:00 - 14:30, 14:00 - 22:30, 22:30 - 06:00. My test won't pull shift 3
times between 22:30 and 6:00(looking accross midnight to the next day). It
pulls times like 8 and 9 AM..

Ken Snell said:
Sid -

If I recall from our other thread, you're building an SQL in code right? So
this snippet is part of the text string that you're building?

When you say it doesn't work, are we just referring to the syntax error that
is being thrown by the compiler? Or that the query doesn't work at all? Is
the form open when you try to run the code?

Assuming that both of my comments above are correct, then you can include
the full form reference as part of the text string and you don't need the #
delimiters. This assumes that you want the query to be stored with the
ability to use those parameters, something that you may not want (as you
then will need to resolve those parameters when you run it -- thus, it would
be better to do it here when you build the query).

Post the whole section of code (the entire part of building the SQL
statement in code, etc.). It may be something subtle that isn't obvious from
what you've posted so far.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Ken
The Sh1B and Sh1E form controls are formatted Short Date at the moment. The
Time_Off and Time_On are stored as Date/Time no format.

This is the whole section of code that doesn't work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E] & "#))
WITH OWNERACCESS OPTION;

This is the whole section that does work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate]) AND ((TimeValue([Time_On])) Between
#12/30/1899 6:0:0# And #12/30/1899 14:30:0#))
WITH OWNERACCESS OPTION;

This last example starts out with with #06:00# and #14:30# , when I save &
close it, Access changes it, it converts my times like #06:00# to
#12/30/1899 6:0:0# It does give my desired results though.
Thanks

Ken Snell said:
Sid -

Post the entire section of code. It's hard to be sure of the ' and "
Click to expand...
Click to expand...
and
#
and stuff from just a snippet. Also, what is the format of the Sh1B
Click to expand...
and
Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
Click to expand...
Click to expand...
#"
&
[Forms]![frmReports]![Sh1B] & "# AND #" &
Click to expand...
Click to expand...
[Forms]![frmReports]![Sh1E]
&
"#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When run,
the cursor is after the semicolon at the end. In the error display, the
Last # is not displayed....
Thanks

I think you had unbalanced parenthesis and missing quotes. Try pasting
in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" &
Click to expand...
Me!Sh1E
&
"#"

provided this line continues a properly constructed SQL string. I am
assuming this is code built inside the form module? If not, you
Click to expand...
need
to
use the complete form reference and the form must be open in order for
the WHERE to work.
Pavel

Sid wrote:

Ok, I thought I had this SQL QueryDef syntax thing mastered
Click to expand...
until
the
date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #"
&(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past
Click to expand...
my
last
#.

I have tried variations of the """" like my other string control
values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
Click to expand...
 
You'll need to use the combination of the date and the time in the criterion
to catch the records that go across midnight. As it appears that you're
storing the date and time separately in your table, you can add to the query
a calculated field that combines the date and time:
CombinedDT: [DateFieldName] + [TimeFieldName]

Then do the WHERE or HAVING on that calculated field instead of the actual
time field, but be sure you use the combination of date and time values from
your form in the syntax.

One other item -- it's often more efficient in your query if you can use a
WHERE statement instead of a HAVING statement for filtering your records.
With WHERE, ACCESS will eliminate unmatched records before doing the SUM or
GROUP BY actions of your totals query; with HAVING, all the records are
totaled first and then the records are filtered. This can mean longer
running times for the query and increased network traffic if you're using a
backend that isn't right on the PC.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Hey its working! Without the # and & and " signs...

My SQL section ended up like this:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
[Forms]![frmReports]![Sh1B] AND [Forms]![frmReports]![Sh1E]))
WITH OWNERACCESS OPTION;
Click to expand...

This is a stand-alone query used for my test of SQL syntax at this point.
I'll probably try to QueryDef this next.

My only remaining challenge is, my three shift times(Sh1, Sh2, Sh3) are
06:00 - 14:30, 14:00 - 22:30, 22:30 - 06:00. My test won't pull shift 3
times between 22:30 and 6:00(looking accross midnight to the next day). It
pulls times like 8 and 9 AM..

Ken Snell said:
Sid -

If I recall from our other thread, you're building an SQL in code right? So
this snippet is part of the text string that you're building?

When you say it doesn't work, are we just referring to the syntax error that
is being thrown by the compiler? Or that the query doesn't work at all? Is
the form open when you try to run the code?

Assuming that both of my comments above are correct, then you can include
the full form reference as part of the text string and you don't need
Click to expand...
the
#
delimiters. This assumes that you want the query to be stored with the
ability to use those parameters, something that you may not want (as you
then will need to resolve those parameters when you run it -- thus, it would
be better to do it here when you build the query).

Post the whole section of code (the entire part of building the SQL
statement in code, etc.). It may be something subtle that isn't obvious from
what you've posted so far.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Ken
The Sh1B and Sh1E form controls are formatted Short Date at the
Click to expand...
moment.
The
Time_Off and Time_On are stored as Date/Time no format.

This is the whole section of code that doesn't work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN #" &
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E]
Click to expand...
&
"#))
WITH OWNERACCESS OPTION;

This is the whole section that does work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate]) AND ((TimeValue([Time_On])) Between
#12/30/1899 6:0:0# And #12/30/1899 14:30:0#))
WITH OWNERACCESS OPTION;

This last example starts out with with #06:00# and #14:30# , when I
Click to expand...
Click to expand...
save
&
close it, Access changes it, it converts my times like #06:00# to
#12/30/1899 6:0:0# It does give my desired results though.
Thanks

Sid -

Post the entire section of code. It's hard to be sure of the ' and "
Click to expand...
and
#
and stuff from just a snippet. Also, what is the format of the Sh1B and
Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
Click to expand...
#"
&
[Forms]![frmReports]![Sh1B] & "# AND #" &
Click to expand...
[Forms]![frmReports]![Sh1E]
&
"#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When
run,
the cursor is after the semicolon at the end. In the error
Click to expand...
display,
the
Last # is not displayed....
Thanks

I think you had unbalanced parenthesis and missing quotes. Try pasting
in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" & Me!Sh1E
&
"#"

provided this line continues a properly constructed SQL string.
Click to expand...
Click to expand...
I
am
assuming this is code built inside the form module? If not, you need
to
use the complete form reference and the form must be open in
Click to expand...
order
for
the WHERE to work.
Pavel

Sid wrote:

Ok, I thought I had this SQL QueryDef syntax thing mastered until
the
date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "# And #"
&(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past my
last
#.

I have tried variations of the """" like my other string control
values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
Click to expand...
 
QueryDefs are even working now too for all shifts.
The final way I selected Shift3(10:30PM - 6:00AM) was to exclude shift1
times(Sh1B, Sh1E) & shift2 times(Sh2B, Sh2E), final QueryDef looks like
this:

....AND TimeValue([Time_On]) NOT BETWEEN [Forms]![frmReports]![Sh1B] AND
[Forms]![frmReports]![Sh2E]));
Sid

Ken Snell said:
You'll need to use the combination of the date and the time in the criterion
to catch the records that go across midnight. As it appears that you're
storing the date and time separately in your table, you can add to the query
a calculated field that combines the date and time:
CombinedDT: [DateFieldName] + [TimeFieldName]

Then do the WHERE or HAVING on that calculated field instead of the actual
time field, but be sure you use the combination of date and time values from
your form in the syntax.

One other item -- it's often more efficient in your query if you can use a
WHERE statement instead of a HAVING statement for filtering your records.
With WHERE, ACCESS will eliminate unmatched records before doing the SUM or
GROUP BY actions of your totals query; with HAVING, all the records are
totaled first and then the records are filtered. This can mean longer
running times for the query and increased network traffic if you're using a
backend that isn't right on the PC.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Hey its working! Without the # and & and " signs...

My SQL section ended up like this:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
[Forms]![frmReports]![Sh1B] AND [Forms]![frmReports]![Sh1E]))
WITH OWNERACCESS OPTION;
Click to expand...

This is a stand-alone query used for my test of SQL syntax at this point.
I'll probably try to QueryDef this next.

My only remaining challenge is, my three shift times(Sh1, Sh2, Sh3) are
06:00 - 14:30, 14:00 - 22:30, 22:30 - 06:00. My test won't pull shift 3
times between 22:30 and 6:00(looking accross midnight to the next day). It
pulls times like 8 and 9 AM..

Ken Snell said:
Sid -

If I recall from our other thread, you're building an SQL in code
Click to expand...
right?
So
this snippet is part of the text string that you're building?

When you say it doesn't work, are we just referring to the syntax
Click to expand...
error
that
is being thrown by the compiler? Or that the query doesn't work at
Click to expand...
Click to expand...
all?
Is
the form open when you try to run the code?

Assuming that both of my comments above are correct, then you can include
the full form reference as part of the text string and you don't need
Click to expand...
the
#
delimiters. This assumes that you want the query to be stored with the
ability to use those parameters, something that you may not want (as you
then will need to resolve those parameters when you run it -- thus, it would
be better to do it here when you build the query).

Post the whole section of code (the entire part of building the SQL
statement in code, etc.). It may be something subtle that isn't
Click to expand...
obvious
from
what you've posted so far.

--
Ken Snell
<MS ACCESS MVP>

Ken
The Sh1B and Sh1E form controls are formatted Short Date at the moment.
The
Time_Off and Time_On are stored as Date/Time no format.

This is the whole section of code that doesn't work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
Click to expand...
Click to expand...
#"
&
[Forms]![frmReports]![Sh1B] & "# AND #" &
Click to expand...
Click to expand...
[Forms]![frmReports]![Sh1E]
&
"#))
WITH OWNERACCESS OPTION;

This is the whole section that does work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate]) AND ((TimeValue([Time_On])) Between
#12/30/1899 6:0:0# And #12/30/1899 14:30:0#))
WITH OWNERACCESS OPTION;

This last example starts out with with #06:00# and #14:30# , when I
Click to expand...
save
&
close it, Access changes it, it converts my times like #06:00# to
#12/30/1899 6:0:0# It does give my desired results though.
Thanks

Sid -

Post the entire section of code. It's hard to be sure of the ' and
Click to expand...
"
and
#
and stuff from just a snippet. Also, what is the format of the
Click to expand...
Sh1B
and
Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND
((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On])
Click to expand...
BETWEEN
#"
&
[Forms]![frmReports]![Sh1B] & "# AND #" & [Forms]![frmReports]![Sh1E]
&
"#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When
run,
the cursor is after the semicolon at the end. In the error display,
the
Last # is not displayed....
Thanks

I think you had unbalanced parenthesis and missing quotes. Try
pasting
in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" &
Me!Sh1E
&
"#"

provided this line continues a properly constructed SQL
Click to expand...
Click to expand...
string.
I
am
assuming this is code built inside the form module? If not,
Click to expand...
you
need
to
use the complete form reference and the form must be open in order
for
the WHERE to work.
Pavel

Sid wrote:

Ok, I thought I had this SQL QueryDef syntax thing mastered until
the
date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "#
Click to expand...
Click to expand...
And
#" past
my
Click to expand...
Click to expand...
 
Creative! Good luck.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
QueryDefs are even working now too for all shifts.
The final way I selected Shift3(10:30PM - 6:00AM) was to exclude shift1
times(Sh1B, Sh1E) & shift2 times(Sh2B, Sh2E), final QueryDef looks like
this:

...AND TimeValue([Time_On]) NOT BETWEEN [Forms]![frmReports]![Sh1B] AND
[Forms]![frmReports]![Sh2E]));
Sid

Ken Snell said:
You'll need to use the combination of the date and the time in the criterion
to catch the records that go across midnight. As it appears that you're
storing the date and time separately in your table, you can add to the query
a calculated field that combines the date and time:
CombinedDT: [DateFieldName] + [TimeFieldName]

Then do the WHERE or HAVING on that calculated field instead of the actual
time field, but be sure you use the combination of date and time values from
your form in the syntax.

One other item -- it's often more efficient in your query if you can use a
WHERE statement instead of a HAVING statement for filtering your records.
With WHERE, ACCESS will eliminate unmatched records before doing the SUM or
GROUP BY actions of your totals query; with HAVING, all the records are
totaled first and then the records are filtered. This can mean longer
running times for the query and increased network traffic if you're
Click to expand...
using
a
backend that isn't right on the PC.

--
Ken Snell
<MS ACCESS MVP>

Sid said:
Hey its working! Without the # and & and " signs...

My SQL section ended up like this:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
[Forms]![frmReports]![Sh1B] AND [Forms]![frmReports]![Sh1E]))
WITH OWNERACCESS OPTION;

This is a stand-alone query used for my test of SQL syntax at this point.
I'll probably try to QueryDef this next.

My only remaining challenge is, my three shift times(Sh1, Sh2, Sh3) are
06:00 - 14:30, 14:00 - 22:30, 22:30 - 06:00. My test won't pull shift 3
times between 22:30 and 6:00(looking accross midnight to the next
Click to expand...
day).
It
pulls times like 8 and 9 AM..

Sid -

If I recall from our other thread, you're building an SQL in code right?
So
this snippet is part of the text string that you're building?

When you say it doesn't work, are we just referring to the syntax error
that
is being thrown by the compiler? Or that the query doesn't work at
Click to expand...
all?
Is
the form open when you try to run the code?

Assuming that both of my comments above are correct, then you can include
the full form reference as part of the text string and you don't
Click to expand...
need
the
#
delimiters. This assumes that you want the query to be stored with the
ability to use those parameters, something that you may not want (as you
then will need to resolve those parameters when you run it -- thus, it
would
be better to do it here when you build the query).

Post the whole section of code (the entire part of building the SQL
statement in code, etc.). It may be something subtle that isn't obvious
from
what you've posted so far.

--
Ken Snell
<MS ACCESS MVP>

Ken
The Sh1B and Sh1E form controls are formatted Short Date at the moment.
The
Time_Off and Time_On are stored as Date/Time no format.

This is the whole section of code that doesn't work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
Click to expand...
#"
&
[Forms]![frmReports]![Sh1B] & "# AND #" &
Click to expand...
[Forms]![frmReports]![Sh1E]
&
"#))
WITH OWNERACCESS OPTION;

This is the whole section that does work:
HAVING (((tblEventLog.Time_Off) Is Not Null) AND ((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate]) AND ((TimeValue([Time_On])) Between
#12/30/1899 6:0:0# And #12/30/1899 14:30:0#))
WITH OWNERACCESS OPTION;

This last example starts out with with #06:00# and #14:30# , when
Click to expand...
I
save
&
close it, Access changes it, it converts my times like #06:00# to
#12/30/1899 6:0:0# It does give my desired results though.
Thanks

Sid -

Post the entire section of code. It's hard to be sure of the '
Click to expand...
Click to expand...
and
"
and
#
and stuff from just a snippet. Also, what is the format of the Sh1B
and
Sh1E
controls on the form? Are they set to one of the Date types?

--
Ken Snell
<MS ACCESS MVP>

Pavel
I corrected my form reference. My latest version looks like this:

HAVING (((tblEventLog.Time_Off) Is Not Null) AND
((tblEventLog.LogDate)
Between [Forms]![frmReports]![BeginningDate] And
[Forms]![frmReports]![Endingdate] AND TimeValue([Time_On]) BETWEEN
#"
&
[Forms]![frmReports]![Sh1B] & "# AND #" &
[Forms]![frmReports]![Sh1E]
&
"#))
WITH OWNERACCESS OPTION;

Your ending & "#" gives an instant syntax error when saved. When
run,
the cursor is after the semicolon at the end. In the error display,
the
Last # is not displayed....
Thanks

I think you had unbalanced parenthesis and missing quotes. Try
pasting
in

... AND TimeValue([TimeOn]) BETWEEN #" & Me!Sh1B & "# And #" &
Me!Sh1E
&
"#"

provided this line continues a properly constructed SQL
Click to expand...
string.
I
am
assuming this is code built inside the form module? If not, you
need
to
use the complete form reference and the form must be open in order
for
the WHERE to work.
Pavel

Sid wrote:

Ok, I thought I had this SQL QueryDef syntax thing mastered
until
the
date
aspect. This is a section of my test query.
(Works great with Actual times:)

...AND ((TimeValue([TimeOn])) BETWEEN #08:00# AND #14:30#

But, referring to a form's controls I fail with:
..AND ((TimeValue([TimeOn])) BETWEEN #(Me!Sh1B)# AND #(Me!Sh1E)#
or
...AND ((TimeValue([TimeOn])) BETWEEN #" & (Me!Sh1B) & "#
Click to expand...
And
#"
&(Me!Sh1E)
& "#

The Syntax Error message cursor is always left at the end past
my
last
#.

I have tried variations of the """" like my other string control
values
also...
Any suggestions appreciated
Sid
Click to expand...
Click to expand...
Click to expand...
 
Back
Top