Criteria Between "7:00" and "11:00" Showing everything but . . . . . . .

[MFS]

Okay this is weird,
I typed this code in my Queries criteria under the time column. . .

Between "7:00" and "11:00"

and this is the data that I get in return.
11:01
11:02
11:03
11:04
5:00
5:03
5:04


etc
etc
etc
6:58
6:59


Hopefully someone can explain what might be happening.

[ItsMe]

If your field does not include an actual date in its value you might consider using the format function. I think in order to use the BETWEEN operator in a Date/Time field it needs a full date. I believe using format function will assume a date, even if one does not exist and a date is not included in the format details.

EDIT: Also, you are not using # as a date qualifier.

[MFS]

I opened the table and I am seeing my data is not entered sequentially.
May show 8 or so entries from 9:00 thru 9:30 then shows entries from 10:00 to 10:30 then entries from 7:00 and so on. (Quantities and times are not exact)
The entries are coming in from a PLC. Whenever a certain output goes high the database collects predefined tag data.

[MFS]

If your field does not include an actual date in its value you might consider using the format function. I think in order to use the BETWEEN operator in a Date/Time field it needs a full date. I believe using format function will assume a date, even if one does not exist and a date is not included in the format details.

EDIT: Also, you are not using # as a date qualifier.

ItsMe, Could you provide an example of the code you would use?
Also, if you remember I'm using textboxes to enter my data into the Query, as discussed last week with the Union Query, Everything seemed to work excellent until I reviewed the data today from over the weekend.

[rpeare]

I think you missed ItsMe's point. how is the data being STORED if your 'time' field is actually being stored as a text value (look at the design view of the source table in access) you will have to convert the text string to an actual value before you can perform any operations on it (i.e. using the between function, figuring time elapsed, etc).

[ItsMe]

To expand on rpeare's comment, because there is a lot not known still....


When I make comparisons of times, I will add a date in front of them (arbitrary date like 01/01/1800). I also want to make sure I am getting AM and PM correct. So I might use the Format() function to ensure the correct translation (I believe if you do not include a date, Access will assign a date for you like 01/01/1800). You can do something similar to the format function by simply using the properties of an unbound or bound textbox on a form. Then it is just a matter of including the # qualifiers in your code as you pass the values to your SQL.
I did a quick search and found this
https://www.accessforums.net/access/...tml#post213778

I would start with formatting and make sure you are including the # qualifier.

Further down the thread June has some links for Dynamic Parameterized queries. You could possibly shift the responsibility to the query object.

[MFS]

My 'time' field is a string file from my PLC yes, being sent to an SQL data table. The data type is a Varchar(MAX). Access is being used as a front end for the users, and the SQL tables are linked to Access.
When I try setting the data type to time(7) or a timestamp, my transaction manager fails because the PLC is sending the time as a string and not a time stamp. So I have to use the data type varchar(MAX).
I tried setting the 'time' field format in the Query as hh:nn:ss,

[MFS]

To expand on rpeare's comment, because there is a lot not known still....


When I make comparisons of times, I will add a date in front of them (arbitrary date like 01/01/1800). I also want to make sure I am getting AM and PM correct. So I might use the Format() function to ensure the correct translation (I believe if you do not include a date, Access will assign a date for you like 01/01/1800). You can do something similar to the format function by simply using the properties of an unbound or bound textbox on a form. Then it is just a matter of including the # qualifiers in your code as you pass the values to your SQL.
I did a quick search and found this
https://www.accessforums.net/access/...tml#post213778

I would start with formatting and make sure you are including the # qualifier.

Further down the thread June has some links for Dynamic Parameterized queries. You could possibly shift the responsibility to the query object.

Ahhh, Okay, I see now, I will try this and then respond.
I am also using Date() in my query as well under the 'date' field.
Thanks, will let you know.

[ItsMe]

Date() is a function that SQL will recognize as data type Date, unlike values from textboxes that may get interpreted as a text value.

[orange]

Also, check out the CDate() function. It may be helpful.

[MFS]

ItsMe,
Okay, I'm still not having any luck with this. I've tried the formatting but have yet to include the # qualifiers.
Where would I add them? I couldn't seem to find anything out there to help me with this.
I'm sure this is easy but for some reason I just cant grasp what you are asking me to do.
My code in my query criteria,

Between [forms]![Cell 4 Device Trending]![text455] And [forms]![Cell 4 Device Trending]![Text457]

[ItsMe]

...I've tried the formatting but have yet to include the # qualifiers.
Where would I add them?...

Formatting is not the answer to everything. You need to tell SQL that this is a type DATE.

example of adding qualifier from the link I provided.
strWhere = (strWhere & " AND ") & "[importdate] BETWEEN #" & [Forms]![repairs]![StartDate] & "# AND #" & [Forms]![repairs]![StartDate] & "#"

[rpeare]

Make a field in your query (this is assuming time past noon is stored as 13:00):

ConvertedTime: cdate([DateField])

this will convert your TEXT value to an actual DATE/TIME value.

Instead of 11:00 you should now see 11:00:00 AM

in your criteria put

>=CDate([forms]![Cell 4 Device Trending]![text455]) And <=CDate([forms]![Cell 4 Device Trending]![Text457])

[MFS]

Now this is making sense,
I am a firm believer to my Engineering Dept. to not provide the answers, provide suggestions and then have them do the homework and figure it out on their own. It's the best way to learn.
I think that is where both of you, "ItsMe" and "repeare" were going with this. You both wanted me to figure this out.
I appreciated the challenge but I simply was not grasping it until I seen it in front of me, and now, well I feel like a total idiot.
Thank you, it seems to work perfect, And I understand how both ways will provide the answer to my request.