Creating a Query through a Form

[weathergirl]

I'm trying to create a search of a table with a form. I'm using the form for easy entering of fields instead of having to go into the query and change the criteria. I'm trying to query different field such as date, location, event, etc. What I want to happen is type in the search parameters they are looking for, hit a button on the form, and a table would be produced with the information fitting their criteria.

I've tried using tilde variables but they are not pulling the information from the table. If any one could give me ideas on the script to run this it would be much appreciated.

Thanks,
Weather Girl

 

[Langley]

When you do a time range query manually, does it work; and what time format are you using? Let's see some sample time data.

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

I tried doing the query manually and it doens't work. I'm guessing it has something to do with the format. I have the time in a format like this 5:50 PM. I tried using >= and <= to do the manual query and I also tried LIKE. Neither of them worked. Even just a simple =Time didn't work. I guess I need to figure out the manual part before the query.

Weather Girl

 

[Langley]

I think that most of the standard time foramts default to HH:MM:SS MM. Try putting in 05:50:00 PM instead and see what happens. If that's the problem, it will be easy to append the ':00 PM' to the end of your users string.

Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

I found that using HH:MM:SS works to run the query manually. I tried adding the AM or PM but then it gave me an error of missing comma. So I need to add the :ss to the string, but how do I do that?

Weather Girl

 

[weathergirl]

I found that using HH:MM:SS works to run the query manually. I tried adding the AM or PM but then it gave me an error of missing comma. So I need to add the :ss to the string, but how do I do that? And what do I do with the AM and PM part?

Weather Girl

 

[Langley]

Try putting the whole thing (with the AM or PM) in quotes during the manual query, i.e. &quot;HH:MM:SS AM&quot;. It doesn't like separations in values ( it thinks it's two values and asks for a comma).

If it was me, I would force them to enter the correct format in the field. Use a picture like this:

{0#,1#,2{0,1,2,3}}:{0,1,2,3,4,5}#:{0,1,2,3,4,5}# {AM,PM}


Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

I'm still having problems with the format. The query works when I use HH:mm:ss but it comes up with no data even when there should be. Is it not able to read the times because of the different formats?

Weather Girl

 

[Langley]

The formats do have to be consistent or you will get no return from the query. For example if you use HH:MM:SS AM in the query, the actual data has to be in that format too.

This is one of the reasons I use four digit military time - it's much simpler to deal with in queries and the algorithum for getting the difference isn't that bad.

Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

I think I changed everything to the same time format. When I use the format I have everything in it gives me all the data like I didn't even enter any search parameters.

Weather Girl

 

[Langley]

I think we've reached the point where you need to show me an actual query and a table with some real data in it. Send it via email.



Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us