SQL Time Syntax referring to a form control

S

Sid

I have a Query that I want to select [TimeOn] values based on reference
[ShiftBegin] & [ShiftEnd] times. My test looked like this & worked fine:

((TimeValue([Time_On])) BETWEEN #08:00# And #14:30#))

When I put my form's Control name in, in place of actual time, my syntax
fails:
((TimeValue([Time_On])) BETWEEN # & [Me!Sh1B] & # And # & [Me!Sh1E] & #)).

I think my problem is in the &'s, (, ),...
Any suggestions appreciated.
Sid
 
D

Duane Hookom

We don't know the context that this comes from. Is this a saved query, row
source property, record source property, code,...? "Me" is only understood
in the module for the form. A WAG is:

"((TimeValue([Time_On])) BETWEEN #" & [Me!Sh1B] & "# And #" & [Me!Sh1E] &
"#))"
 
S

Sid

Duane
Its a Query. Thanks for the Me! info, that will help. I'm trying your below
suggestion in my SQL.
Thanks
Duane Hookom said:
We don't know the context that this comes from. Is this a saved query, row
source property, record source property, code,...? "Me" is only understood
in the module for the form. A WAG is:

"((TimeValue([Time_On])) BETWEEN #" & [Me!Sh1B] & "# And #" & [Me!Sh1E] &
"#))"

--
Duane Hookom
MS Access MVP


Sid said:
I have a Query that I want to select [TimeOn] values based on reference
[ShiftBegin] & [ShiftEnd] times. My test looked like this & worked fine:

((TimeValue([Time_On])) BETWEEN #08:00# And #14:30#))

When I put my form's Control name in, in place of actual time, my syntax
fails:
((TimeValue([Time_On])) BETWEEN # & [Me!Sh1B] & # And # & [Me!Sh1E] & #)).

I think my problem is in the &'s, (, ),...
Any suggestions appreciated.
Sid
Click to expand...
Click to expand...
 
D

Duane Hookom

If this was a saved query then you will need to use
TimeValue([Time_On]) BETWEEN & Forms!frmA!Sh1B & And & Forms!frmA!Sh1E

--
Duane Hookom
MS Access MVP


Sid said:
Duane
Its a Query. Thanks for the Me! info, that will help. I'm trying your below
suggestion in my SQL.
Thanks
Duane Hookom said:
We don't know the context that this comes from. Is this a saved query, row
source property, record source property, code,...? "Me" is only understood
in the module for the form. A WAG is:

"((TimeValue([Time_On])) BETWEEN #" & [Me!Sh1B] & "# And #" & [Me!Sh1E] &
"#))"

--
Duane Hookom
MS Access MVP


Sid said:
I have a Query that I want to select [TimeOn] values based on reference
[ShiftBegin] & [ShiftEnd] times. My test looked like this & worked fine:

((TimeValue([Time_On])) BETWEEN #08:00# And #14:30#))

When I put my form's Control name in, in place of actual time, my syntax
fails:
((TimeValue([Time_On])) BETWEEN # & [Me!Sh1B] & # And # & [Me!Sh1E] & #)).

I think my problem is in the &'s, (, ),...
Any suggestions appreciated.
Sid
Click to expand...
Click to expand...
Click to expand...
 
S

Sid

Wow Duane!
That worked perfect. (it didn't like the & signs) How is it that the # signs
aren't required when using a form control as a source?
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;
Thank you, Thank you.
Sid
Duane Hookom said:
If this was a saved query then you will need to use
TimeValue([Time_On]) BETWEEN & Forms!frmA!Sh1B & And & Forms!frmA!Sh1E

--
Duane Hookom
MS Access MVP


Sid said:
Duane
Its a Query. Thanks for the Me! info, that will help. I'm trying your below
suggestion in my SQL.
Thanks
Duane Hookom said:
We don't know the context that this comes from. Is this a saved query, row
source property, record source property, code,...? "Me" is only understood
in the module for the form. A WAG is:

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

--
Duane Hookom
MS Access MVP


I have a Query that I want to select [TimeOn] values based on reference
[ShiftBegin] & [ShiftEnd] times. My test looked like this & worked fine:

((TimeValue([Time_On])) BETWEEN #08:00# And #14:30#))

When I put my form's Control name in, in place of actual time, my syntax
fails:
((TimeValue([Time_On])) BETWEEN # & [Me!Sh1B] & # And # & [Me!Sh1E]
Click to expand...
&
#)).
I think my problem is in the &'s, (, ),...
Any suggestions appreciated.
Sid
Click to expand...
Click to expand...
Click to expand...
 
S

Sid

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..
Thanks
Sid said:
Wow Duane!
That worked perfect. (it didn't like the & signs) How is it that the # signs
aren't required when using a form control as a source?
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;
Thank you, Thank you.
Sid
Duane Hookom said:
If this was a saved query then you will need to use
TimeValue([Time_On]) BETWEEN & Forms!frmA!Sh1B & And & Forms!frmA!Sh1E

--
Duane Hookom
MS Access MVP


Sid said:
Duane
Its a Query. Thanks for the Me! info, that will help. I'm trying your below
suggestion in my SQL.
Thanks
We don't know the context that this comes from. Is this a saved
Click to expand...
query,
row
source property, record source property, code,...? "Me" is only understood
in the module for the form. A WAG is:

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

--
Duane Hookom
MS Access MVP


I have a Query that I want to select [TimeOn] values based on reference
[ShiftBegin] & [ShiftEnd] times. My test looked like this & worked
fine:

((TimeValue([Time_On])) BETWEEN #08:00# And #14:30#))

When I put my form's Control name in, in place of actual time, my syntax
fails:
((TimeValue([Time_On])) BETWEEN # & [Me!Sh1B] & # And # &
Click to expand...
Click to expand...
[Me!Sh1E]
&
Click to expand...
Click to expand...
 
S

Sid

I solved it.
My resulting QueryDef SQL looks like this:
....AND TimeValue([Time_On]) NOT BETWEEN [Forms]![frmReports]![Sh1B] AND
[Forms]![frmReports]![Sh2E]));
Sid
Sid said:
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..
Thanks
Sid said:
Wow Duane!
That worked perfect. (it didn't like the & signs) How is it that the # signs
aren't required when using a form control as a source?
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;
Thank you, Thank you.
Sid
Duane Hookom said:
If this was a saved query then you will need to use
TimeValue([Time_On]) BETWEEN & Forms!frmA!Sh1B & And & Forms!frmA!Sh1E

--
Duane Hookom
MS Access MVP


Duane
Its a Query. Thanks for the Me! info, that will help. I'm trying your
below
suggestion in my SQL.
Thanks
We don't know the context that this comes from. Is this a saved query,
row
source property, record source property, code,...? "Me" is only
understood
in the module for the form. A WAG is:

"((TimeValue([Time_On])) BETWEEN #" & [Me!Sh1B] & "# And #" & [Me!Sh1E]
&
"#))"

--
Duane Hookom
MS Access MVP


I have a Query that I want to select [TimeOn] values based on
reference
[ShiftBegin] & [ShiftEnd] times. My test looked like this & worked
fine:

((TimeValue([Time_On])) BETWEEN #08:00# And #14:30#))

When I put my form's Control name in, in place of actual time, my
syntax
fails:
((TimeValue([Time_On])) BETWEEN # & [Me!Sh1B] & # And # &
Click to expand...
[Me!Sh1E]
&
#)).

I think my problem is in the &'s, (, ),...
Any suggestions appreciated.
Sid
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

Ken or Doug? -- another SQL syntax stumper 9
Criteria, Between two times... 2
How to Calculate if a Range of Times Falls Between two Date/Time S 1
Syntax to Average a time duration (hh:mm:ss). 14
SQL syntax for Access Pass Through queries (ODBC) 4
Referring to control value on form using variable name for control 5
Referring to text control in a form 2
Referring to a control on another form 5

Top