Re: Getting data from form into a SQL statement

anonymous_at_discussions.microsoft.com
Date: 04/26/04 

Date: Mon, 26 Apr 2004 12:12:53 -0700

Thanks

I tried the following SQL but get a compile error: syntax
error but I can't see what's wrong:

Private Sub Command14_Click()

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.Date)=forms![SINGLE BOOKING
AVAILABILITY]![BookingDate]) AND ((AVAILABILITY.Period)
=forms![SINGLE BOOKING AVAILABILITY]![Combo8]) AND
((AVAILABILITY.Room)=forms![SINGLE BOOKING AVAILABILITY]!
[Combo10]));

End Sub

CAn you see the problem?

>-----Original Message-----
>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.
>.
>