put sql into a text box, how? (1 Viewer)

[shutzy]

i have got the result i need but i got by using a query. no i need to convert it in someway to be able to put it in a text bob. is it possible and does anyone know how to do it.

SELECT tblClientDetails.ClientDetailsID, tblClientCourse.ItemsRemaining, tblCourseItems.ItemsID
FROM tblClientDetails INNER JOIN (((tblCourses INNER JOIN tblClientCourse ON tblCourses.CourseID = tblClientCourse.CourseID) INNER JOIN tblOrdersItems ON tblClientCourse.OrdersItemsID = tblOrdersItems.OrdersItemsID) INNER JOIN tblCourseItems ON tblCourses.CourseID = tblCourseItems.CoursesID) ON tblClientDetails.ClientDetailsID = tblClientCourse.ClientID
WHERE (((tblClientDetails.ClientDetailsID)=[Forms]![frmDepartures]![ClientDetailsID]) AND ((tblClientCourse.ItemsRemaining)>0) AND ((tblCourseItems.ItemsID)=[Forms]![frmDepartures-PriceChange]![ItemsID]));

thanks

 

[pbaldy]

You probably know you can't use it directly in a textbox. You could use a DLookup() to get the value from the query if it only returns one record. Other options include making the SQL the row source of a combo or list box, and opening a recordset on the SQL and placing the value in the textbox in code.

 

[shutzy]

That was exactly the answer I was looking for. So just to clarify. I can create a query and put a dlookup in a text box to return the correct value. I made one change to the SQL. I put the [itemsremaining] as first do it should only return 1 value. Thanks for the help.

 

[pbaldy]

No problem.

 

[shutzy]

hello again, im struggling with the DLookup. i dont really know how to do a DLookup for a query as it is very new to me. about 10 minutes old.lol. ive tried and i get #NAME in my txt box.what am i missing

=DLookUp([ItemsRemaining],[qryDepartures-PriceChange-CourseItemsRemaining])

 

[pbaldy]

They are string arguments. Try

=DLookUp("[ItemsRemaining]","[qryDepartures-PriceChange-CourseItemsRemaining]")

 

[shutzy]

always those little things, isnt it! thanks

 

[pbaldy]

Yes, it is. This is where you want to whack the monitor and yell "you know what I meant!!"