May 24, 2010 at 9:40 am
Hello i was able to migrate an Access application to sql.
this is one of the things that i am stuck in.
SELECT Job.[Client Company], Job.[Client POC], Job.[Client Last Name], Job.[Client Office No], Job.[SR City], Job.[SR State], Job.quote1, Job.quote2, Job.[Store Name], Job.[Store No], Job.[Status Date], LastHistoryNote.LastOfRegarding, Job.[SP Company Name], Job.[Next followup], Job.[Profit Amount], Job.[SR Initiation Date], Job.[SP POC], Job.[SP Office No]
FROM Job INNER JOIN LastHistoryNote ON Job.[Tracking No] = LastHistoryNote.[Tracking No]
WHERE (((Job.[Next followup])>[From what Follow up date? m/dd/yyyy] And (Job.[Next followup])<[To what follow up date? m/dd/yyyy]) AND ((Job.JobComplete)=False) AND ((Job.Status)="3. Pending quote approval"))
ORDER BY Job.[SP Company Name], Job.[Next followup];
the query above was in access. how do i create the same function in sql. i am trying to create a report based on the query. the data is viewed based on the selected [sp company name] in the form opened.
thank you in advance
May 24, 2010 at 9:48 am
sorry
this is the right query i need to be converted..
SELECT exxonTesting.[SP Company Name], exxonTesting.[SP Office No], exxonTesting.[Safety POC], exxonTesting.[API Requirements], exxonTesting.City, exxonTesting.State, exxonTesting.[Name Tested], exxonTesting.Completed, exxonTesting.Expires, exxonTesting.[API Key Number]
FROM exxonTesting
WHERE (((exxonTesting.[SP Company Name])=[forms]![Job2]![SP Company Name]));
thank you
May 24, 2010 at 12:16 pm
ehamouda (5/24/2010)
sorrythis is the right query i need to be converted..
SELECT exxonTesting.[SP Company Name], exxonTesting.[SP Office No], exxonTesting.[Safety POC], exxonTesting.[API Requirements], exxonTesting.City, exxonTesting.State, exxonTesting.[Name Tested], exxonTesting.Completed, exxonTesting.Expires, exxonTesting.[API Key Number]
FROM exxonTesting
WHERE (((exxonTesting.[SP Company Name])=[forms]![Job2]![SP Company Name]));
thank you
The above SQL query appears functional except for the WHERE clause. It looks like you're referecing the name of TextBox objects or something on an Access form. If you're porting this SQL to a stored procedure, then you would instead accept Company Name as an input parameter like this:
WHERE exxonTesting.[SP Company Name] = @company_name
The above would require an exact match, and the modification below would do a truncated comparison on the left most characters, which is what users typically want:
WHERE exxonTesting.[SP Company Name] like @company_name + '%'
Also, these columns with spces in the name will technically work, so long as you always enclose them in [brackets], but I would suggest at a minimum replacing spaces with underscore _ characters, while you're in the process of porting this application to SQL Server.
For example: sp_office_no
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply