SQL input

[minoad]

I am trying to rig a very poorly written access database. One of the queries in this database is using information supplied by the user via an inputbox. The input box is being generated in the sql as the below snippet shows:
- Between [Enter Start Date (_ _/_ _/_ _ _ _)] And [Enter End Date (_ _/_ _/_ _ _ _)]))

What i need to do is use the information that is entered into the start date a second time. I tried to add an " AS txtStartDate " but this generated a syntax error. Anyone have any ideas here.

Micah A. Norman

 

[PHV]

Simply use EXACTLY the same parameter:
... Between [Enter Start Date (_ _/_ _/_ _ _ _)] And [Enter End Date (_ _/_ _/_ _ _ _)] AND someField >= [Enter Start Date (_ _/_ _/_ _ _ _)] ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[minoad]

what i need to do is use this data in a report that relies on this query. Is thier any way to pass this to a report. Also if i use the same parameter again wont it require the user to enter it a second time?

 

[PHV]

SELECT ..., [Enter Start Date (_ _/_ _/_ _ _ _)] As txtStartDate
FROM ...
WHERE ... Between [Enter Start Date (_ _/_ _/_ _ _ _)] And [Enter End Date (_ _/_ _/_ _ _ _)]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[minoad]

This is driving me up a wall. Below is my SQL. For whatever reason when it gets to the final line with dateMonthYear it is asking me to enter it. Yet i have already created it using the " as DateMonthYear" any ideas why it seems to forget this fact.

Micah A. Norman

SELECT [Salutation] & " " & [FirstName] & " " & [LastName] AS Fullname, [tblPatient].[Address1], [tblPatient].[Address2], [tblPatient].[Address3], [tblPatient].[City], [tblPatient].[State], [tblPatient].[Zip], [tblPatient].[HomePhone], [tblPatient].[WorkPhone], [tblPatient].[CellPhone], [tblPatient].[OtherPhone], [tblPatient].[qryRightSales_RetestDate], [tblPatient].[qryLeftSales_RetestDate], [Enter Month] AS dateMonthYear
FROM tblPatient
WHERE qryRightSales_RetestDate = dateMonthYear;

 

[PHV]

WHERE qryRightSales_RetestDate = [Enter Month]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

you can't use an alias within the same query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design