Re: Getting data from form into a SQL statement

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: fredg (fgutkind_at_example.invalid)
Date: 04/26/04 

Date: Mon, 26 Apr 2004 18:27:43 GMT

On Mon, 26 Apr 2004 09:27:45 -0700, Brian C wrote:

> I have a form called SINGLE BOOKING AVAILABILITY with
> three input field called Date, Combo8 and Combo10 and a
> command button.
>
> I want to put the values from these fields into a SQL
> statement attached to the command button, i.e.
>
> SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
> AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
> FROM AVAILABILITY
> WHERE (((AVAILABILITY.DATE)=date from form) AND
> (AVAILABILITY.PERIOD)=combo8 from form) AND
> (AVAILABILITY.ROOM)=combo10 from form));
>
> How do I do this? How do I get the data out of the form
> and into my query once the command button is clicked?
>
> Thanks in advance.
 
Use the
forms!FormName!ControlName
syntax.

SELECT AVAILABILITY.DATE, AVAILABILITY.PERIOD,
AVAILABILITY.ROOM, AVAILABILITY.BOOKING ID
FROM AVAILABILITY
WHERE (((AVAILABILITY.DATE)= forms![
SINGLE BOOKING AVAILABILITY]!FormDateFieldName] AND
(AVAILABILITY.PERIOD)=forms![
SINGLE BOOKING AVAILABILITY]![combo8]) AND
(AVAILABILITY.ROOM)=forms![
SINGLE BOOKING AVAILABILITY]![combo10] ));

The form must be open when this query is run.

Two more suggestions.
1) It's not a good idea to use spaces within Access field or Table
names. Make sure you surround the form name with brackets [] (as I
have) so Access knows it's one object name, not 3 individual unknowns.

2) If you really do have a field named [Date] I would strongly suggest
you change it to something else, i.e. dteBookingDate, or dteEventDate,
or simply dteDate.

Date is a Reserved Access/VBA word and should not be used as a
field/object name.
See the appropriate Microsoft KnowledgeBase article for your version
of Access for additional Reserved words.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access' 

-- 
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Quantcast