Trouble getting date value from field

[RussCRM]

I'm trying to do an append query where I add records to my "Services"
table where "Option1" (in this case bound to a checkbox on a form)
from my Guest table is "true". I will be adding the following fields
to my Services table: ServicesLink (a link to the GuestID in my guest
table), ServicesDate (comes from the form I am using to display the
checkmarks) and ServicesType, which I am adding through an expression.

The ServicesLink and ServicesType are adding fine, but for some
reason, I can't get the date from the txtServiceDate field from my
form. I think I might have the syntax wrong.

Could someone look at my code and let me know?

INSERT INTO Services ( ServicesLink, ServicesDate, ServicesType )
SELECT Services.ServicesLink, Forms!frmServicesEntry!txtServiceDate AS
NewServicesDate, "Breakfast" AS NewServiceType
FROM Guest INNER JOIN Services ON Guest.ID = Services.ServicesLink
GROUP BY Services.ServicesLink, Forms!frmServicesEntry!txtServiceDate,
"Breakfast", Guest.Option1
HAVING (((Guest.Option1)=-1));

 

[John W. Vinson]

I'm trying to do an append query where I add records to my "Services"
table where "Option1" (in this case bound to a checkbox on a form)
from my Guest table is "true". I will be adding the following fields
to my Services table: ServicesLink (a link to the GuestID in my guest
table), ServicesDate (comes from the form I am using to display the
checkmarks) and ServicesType, which I am adding through an expression.

The ServicesLink and ServicesType are adding fine, but for some
reason, I can't get the date from the txtServiceDate field from my
form. I think I might have the syntax wrong.

Try adding it as a literal, delimited with #, rather than treating an external
form control as if it were a field in the table. IN addition, you can't pull
ServicesLink FROM table Services in order to INSERT it into Services! What is
the datatype of ServicesLink and how are the tables related?

Also - why the Group By? How can you Group By constants - the service date and
"Breakfast"? If you must Group, do change the HAVING clause to a WHERE.


INSERT INTO Services ( ServicesLink, ServicesDate, ServicesType )
SELECT Services.ServicesLink, "#" &
Format([Forms]![frmServicesEntry]![txtServiceDate], "mm/dd/yyyy") & "#" ,
"Breakfast"
FROM Guest INNER JOIN Services ON Guest.ID = Services.ServicesLink
WHERE (((Guest.Option1)=-1))
GROUP BY Services.ServicesLink;

 

[RussCRM]

This does give me a date value from my form field, but then I get
#04/08/2008# instead of 04/08/2008 for some reason. The other two
fields are great.

 

[RussCRM]

Nevermind, I got it! Thanks!