Criteria

[XaloRichie]

Is it Possible to open a query and for the criteria to be equal to the value stored in a public varible? If so can you give mee an example of how.
Thanks

 

[AncientOne]

You could assign the variable to a function. Then call the function by putting it in the criteria line of a query as:

StoreVar() , where that is the name of your function

 

[XaloRichie]

You could assign the variable to a function. Then call the function by putting it in the criteria line of a query as:

StoreVar() , where that is the name of your function

Maybe if I elbaorate you'll See what i'd like to do and if it is possible. I am going round in circles!

Im writing a tool hire database. The User has selected a customer and the desired tool and has also entered start and end dates. All of which are held in Variables.

I want to check the "Orders" Table to make Sure The requested tool is not Already booked for hire During this time. How do I read the data from the table into say other variables so i can perform the calculations for date clashing ?

Or am I Missing a chunk?
Thanks for your time
Rich

 

[AncientOne]

I am going to ask why you have made these things variables. As far as I can see, they don't need to be. If the user enters the requested dates into a form, you can use the field values as the criteria in an SQL statement or saved query, which runs when you click a command button on the form. Have you got a reason for not doing it this way?

 

[XaloRichie]

I am going to ask why you have made these things variables. As far as I can see, they don't need to be. If the user enters the requested dates into a form, you can use the field values as the criteria in an SQL statement or saved query, which runs when you click a command button on the form. Have you got a reason for not doing it this way?

EERM! Really it is just because They select the customer first which opens a form displaying the customer details and includes a sub form showing history of orders, To add a new order they click a button and I didn't really want to open another form on screen.Just to know the Item is available! I'm not sure if this is a good reason though! And i will Try you idea and see if it is alright. I suppose I could make the form invisible to the user:?

Thanks very much, no doubt I'll be back wingeing about something else soon. Regards. Rich

 

[AncientOne]

If you get an order in for a specific item, wouldn't it be better to check its availability before you invoke the customer history?

 

[XaloRichie]

I have made some progress thanks to your help.

And now I have another question.

If I have a query which runs and selects a record according to specified criteria. Is there a simple way of finding out if the query has a record or not.
Eg. if i queried a table of sales with criteria of a price say =$99.00
and there was not a sale of this amount then the query would not have a record, how do i say "Does the query result with a record or empty"

Do I have to D lookup for the value and then say if (price field in query) ,. $99.00 or is there a slick way ??

Thanks again for all your help.

 

[AncientOne]

If you just want to check for an empty recordset, you can use the BOF and EOF properties of a recordset. If a recordset is at the beginning AND at the end of a rs, it is empty. There's an example in Help.

If you want to know the actual count, there's the Recordcount property. You open a recordset and move to the last record. You can then return the recordcount value of the rs.

If the query is underlying a form, you can use the RecordsetClone property to obtain the Recordcount in a similar way.