When you create a query in MS Access, say a selection query based on a linked table (a table on a SQL server). You can either view it in design view and fill in what fields you want to show in the result, you can write conditions on what values you want to show, select sorting order etc etc or you can view it in SQL view (this means that MS Access creates the SQL code from the selctions you've made i. e. what tables you want to include, what fields you want shown, what conditions you've stated etc) Let me show you a simple selection query on a linked table.
Originally Posted by MarcusJT
This query works perfectly, don't mind the swedish language, I'm sure you get the idea =) Anyway what's interesting here is (I'll translate it for you so you follow more easy):
SELECT OIS_cForlag.lForlagsNr AS Förlag, OIS_cArtikel.szArtikelNr AS ArtikelNr, OIS_cArtikel.szArbetsTitel AS Arbetstitel,
OIS_oArtOIS.lSaldo AS Kvantitet, OIS_oArtOIS.dLagerVarde AS [Lagervärde/styck], OIS_oArtOIS!lSaldo*OIS_oArtOIS!dLagerVarde AS [Totalt lagervärde]
FROM OIS_cForlag, OIS_oArtOIS INNER JOIN OIS_cArtikel ON OIS_oArtOIS.szArtikelNr = OIS_cArtikel.szArtikelNr
WHERE (((OIS_cForlag.lForlagsNr) Between [Ange minsta förlagsnr:] And [Ange största förlagsnr:])
AND ((OIS_cArtikel.szArtikelNr)=[OIS_oArtOIS]![szArtikelNr]) AND ((OIS_oArtOIS.lSaldo)>0)
ORDER BY OIS_cForlag.lForlagsNr;
When the query is runned in MS Access the values "Between ... And..." is required to perform the action, so MS Access asks, whatever it says between the brackets. So two diologboxes appears when you run the query looking like this:
WHERE (((OIS_cDistributor.lDistributorNr) Between [State the smallest distributor nr:] And [State the largest distributor nr:])
State the smallest distributor nr:
State the largest distributor nr:
This makes it able for the user to input a range of distributors he/she wants to observe. This is possible because MS Access interprets the "MS Access SQL code"... but when I'm talking directly to the SQL server I need to use the correct SQL code, not the "MS Access SQL code". So I'm not able in the same way to prompt the user for input values. Thats the whole problem. So the dates I've programmed in the SQL code (in the Pass-through query speaking directly to the SQL server) are fixed. As shown in previous code
I want to be able to change these without having to go in to "SQL programing mode" since the whole purpose with MS Access is to be user friendly and not require of a user to know how to program in SQL.
SELECT @startdate = '20040101 00:00:00'
SELECT @startdate_later = '20040101 00:00:00'
SELECT @enddate = '20040331 00:00:00'
I hope that clarified things!