Dlookup and Date Range...Any Idea

[haj1503]

Hi friends,....

I get something funny job regarding dlookup function, i also know how to use Dlookup in my database but when i need to use dlookup function, what i will thinking first is performance of my Database because when we use Dlookup function is will effect efficiency of performance database, so do you have any idea to solve this matter either in query or module? Please see carefully for this sample table:

Fields: Date / DE1 / DE2 / DE3 / DE4
15/03/2001 15 12 14 12.5
29/03/2001 30 25 28 26
15/04/2001 39 36 37 36
30/04/2001 45 43 44 44

so i need make comparison between BeginDate and EndDate for DE1 / DE2 / DE3 / DE4 this mean EndDate for DE1 compare with BeginDate for DE1 and so on.
If is possible please let me know, you assistance must me appreciated.

Best Regards,
Haj1503

 

[tlbroadbent]

Try this query. It utilizes sub queries plus the DMax and DMin functions.

SELECT a.MinDate, b.MaxDate, a.MinDE1, b.maxDE1, a.MinDE2, b.maxDE2, a.MinDE3, b.maxDE3, a.MinDE4, b.maxDE4
FROM [SELECT RecDate As MinDate, DE1 As MinDE1, DE2 As MinDE2, DE3 As MinDE3, DE4 As MinDE4
FROM TekTips1
WHERE RecDate=DMin("[RecDate]","TekTips1&quot]. AS a, [SELECT RecDate As MaxDate, DE1 AS maxDE1, DE2 AS maxDE2, DE3 AS maxDE3, DE4 AS maxDE4
FROM TekTips1
WHERE TekTips1.RecDate=DMax("[RecDate]","TekTips1&quot]. AS b; Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[haj1503]

Hi Terry,

Thanks for you thread but i am very appreaciated if you can explain to me regarding for such as below:

a; b; RecDate and TikTips1 where this source come up.

thanks again for you thread.

 

[tlbroadbent]

The query is a sample. I didn't know the names of your table and columns so I created names. You will need to replace the names I used with the names of your table and columns.

"a" and "b" are called aliases. SQL allows you to rename an table or query by using an alias. In some cases, this is done for convenience or readability. In the case of the subquery, an alias was required. You can use any name you like for aliases. I like to use short names, particularly one letter aliases.

I used the name RecDate for the Date column. I recommend that you not use the name "Date" for a column because "Date" is a reserved word and using it for a column name can cause problems. It is good programming practice to avoid using any reserved words as column, field or object names. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[haj1503]

Hi Terry,

Please see this SQL Query that i have change from you sample:
SELECT a.MinDate, b.MaxDate, a.MinDE1-Gen_A+, b.maxDE1-Gen_A+, a.MinDE2-Gen_A+, b.maxDE2-Gen_A+, a.MinDE3-Gen_A+, b.maxDE3-Gen_A+, a.MinDE4-Gen_A+, b.maxDE4-Gen_A+
FROM [SELECT Prod_DE.Date AS MinDate, Prod_DE.[DE1-Gen_A+] AS [MinDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MinDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MinDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A],
FROM Prod_DE
WHERE Date=DMin("[Date]","Prod_DE&quot]. AS a, [SELECT Prod_DE.Date AS MaxDate, Prod_DE.[DE1-Gen_A+] AS [MaxDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MaxDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MaxDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MaxDE4-Gen_A]
FROM Prod_DE
WHERE Prod_DE.Date=DMax("[Date]","Prod_DE&quot]. AS b;

but get message " Syntax Error (Missing Operator) in query expression 'a.MinDE1-Gen_A+'.

Please tell me actually for this problem....

 

[tlbroadbent]

You need to remove the "+" from the column names in the first select list. The aliases that you used don't have the "+" suffix.

SELECT a.MinDate, b.MaxDate, a.MinDE1-Gen_A, b.maxDE1-Gen_A, a.MinDE2-Gen_A, b.maxDE2-Gen_A, a.MinDE3-Gen_A, b.maxDE3-Gen_A, a.MinDE4-Gen_A, b.maxDE4-Gen_A
Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[haj1503]

Hi Terry,

come again, sorry cause entrape you again, i still get message but difference the last one, the message something like this " syntax error in FROM Clause. "
the SQL Query something like this
SELECT a.MinDate, b.MaxDate, a.MinDE1-Gen_A, b.maxDE1-Gen_A, a.MinDE2-Gen_A, b.maxDE2-Gen_A, a.MinDE3-Gen_A, b.maxDE3-Gen_A, a.MinDE4-Gen_A, b.maxDE4-Gen_A
FROM [SELECT Prod_DE.Date AS MinDate, Prod_DE.[DE1-Gen_A+] AS [MinDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MinDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MinDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A],
FROM Prod_DE
WHERE Date=DMin("[Date]","Prod_DE&quot]. AS a, [SELECT Prod_DE.Date AS MaxDate, Prod_DE.[DE1-Gen_A+] AS [MaxDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MaxDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MaxDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MaxDE4-Gen_A]
FROM Prod_DE
WHERE Prod_DE.Date=DMax("[Date]","Prod_DE&quot]. AS b;

Sorry, actually i am not fimiliar with SQL but i need to know and very interesting to learn. thanks again and see you later......Please

 

[tlbroadbent]

You have an extra comma "," in the following line which is just before the 2nd FROM in your query.

Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A],

Remove the comma. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[haj1503]

Hi Terry,

See you again, i am very appriciated if you can explian to me again regarding this matter, the problem is i get error message " syntax error in FROM Clause " can you see again please...

SELECT a.MinDate, b.MaxDate, a.MinDE1-Gen_A, b.maxDE1-Gen_A, a.MinDE2-Gen_A, b.maxDE2-Gen_A, a.MinDE3-Gen_A, b.maxDE3-Gen_A, a.MinDE4-Gen_A, b.maxDE4-Gen_A
FROM [SELECT Prod_DE.Date AS MinDate, Prod_DE.[DE1-Gen_A+] AS [MinDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MinDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MinDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A],
FROM Prod_DE
WHERE Date=DMin("[Date]","Prod_DE&quot]. AS a, [SELECT Prod_DE.Date AS MaxDate, Prod_DE.[DE1-Gen_A+] AS [MaxDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MaxDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MaxDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MaxDE4-Gen_A]
FROM Prod_DE
WHERE Prod_DE.Date=DMax("[Date]","Prod_DE&quot]. AS b;

 

[tlbroadbent]

As I explained in my previous post, you must remove the comma before the word FROM. The revised query would look like the following.

SELECT
a.MinDate, b.MaxDate, a.MinDE1-Gen_A,
b.maxDE1-Gen_A, a.MinDE2-Gen_A, b.maxDE2-Gen_A,
a.MinDE3-Gen_A, b.maxDE3-Gen_A, a.MinDE4-Gen_A,
b.maxDE4-Gen_A
FROM

[SELECT Prod_DE.Date AS MinDate,
Prod_DE.[DE1-Gen_A+] AS [MinDE1-Gen_A],
Prod_DE.[DE2-Gen_A+] AS [MinDE2-Gen_A],
Prod_DE.[DE3-Gen_A+] AS [MinDE3-Gen_A],
Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A]
FROM Prod_DE
WHERE Date=DMin("[Date]","Prod_DE&quot]. AS a,

[SELECT Prod_DE.Date AS MaxDate,
Prod_DE.[DE1-Gen_A+] AS [MaxDE1-Gen_A],
Prod_DE.[DE2-Gen_A+] AS [MaxDE2-Gen_A],
Prod_DE.[DE3-Gen_A+] AS [MaxDE3-Gen_A],
Prod_DE.[DE4-Gen_A+] AS [MaxDE4-Gen_A]
FROM Prod_DE
WHERE Prod_DE.Date=DMax("[Date]","Prod_DE&quot]. AS b; Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[haj1503]

Thanks again for you Terry.....
i have followed from your sample SQL Query but i have error message again something like "Invalid bracketing of name SELECT PROD_DE.Date As MinDate, PROD_DE.[DE1-Gen_A+'. "

Thanks again for your sample SQL QUery. I hope that you don't give-up to solve this matter.

 

[tlbroadbent]

First, examine the query and make sure it matches what I posted, exactly. If it does, please post your query again. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[haj1503]

Hi Terry,

Actually what i have wrote in my SQL Query is exactly with SQL Query that your have wrote. i am sure but i got message like this "Invalid bracketing of name SELECT PROD_DE.Date As MinDate, PROD_DE.[DE1-Gen_A+'. "

for your review this is Query SQL that i've wrote :

SELECT a.MinDate, b.MaxDate, a.MinDE1-Gen_A, b.maxDE1-Gen_A, a.MinDE2-Gen_A, b.maxDE2-Gen_A, a.MinDE3-Gen_A, b.maxDE3-Gen_A, a.MinDE4-Gen_A, b.maxDE4-Gen_A FROM [SELECT Prod_DE.Date AS MinDate, Prod_DE.[DE1-Gen_A+] AS [MinDE1-Gen_A], Prod_DE.[DE2Gen_A+] AS [MinDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MinDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A]
FROM Prod_DE WHERE Date=DMin("[Date]","Prod_DE&quot]. AS a, [SELECT Prod_DE.Date AS MaxDate, Prod_DE.[DE1-Gen_A+] AS [MaxDE1-Gen_A], Prod_DE.[DE2-Gen_A+] AS [MaxDE2-Gen_A], Prod_DE.[DE3-Gen_A+] AS [MaxDE3-Gen_A], Prod_DE.[DE4-Gen_A+] AS [MaxDE4-Gen_A] FROM Prod_DE WHERE Prod_DE.Date=DMax("[Date]","Prod_DE&quot]. AS b;

Please Review and let me know if i've wrong writing in this SQL Query.

Best Regards,
Haj1503

 

[tlbroadbent]

Try replacing the [] around the sub-queries with () as follows.

SELECT
a.MinDate, b.MaxDate, a.MinDE1-Gen_A,
b.maxDE1-Gen_A, a.MinDE2-Gen_A, b.maxDE2-Gen_A,
a.MinDE3-Gen_A, b.maxDE3-Gen_A, a.MinDE4-Gen_A,
b.maxDE4-Gen_A
FROM

(SELECT Prod_DE.Date AS MinDate,
Prod_DE.[DE1-Gen_A+] AS [MinDE1-Gen_A],
Prod_DE.[DE2-Gen_A+] AS [MinDE2-Gen_A],
Prod_DE.[DE3-Gen_A+] AS [MinDE3-Gen_A],
Prod_DE.[DE4-Gen_A+] AS [MinDE4-Gen_A]
FROM Prod_DE
WHERE Date=DMin("[Date]","Prod_DE&quot) AS a,

(SELECT Prod_DE.Date AS MaxDate,
Prod_DE.[DE1-Gen_A+] AS [MaxDE1-Gen_A],
Prod_DE.[DE2-Gen_A+] AS [MaxDE2-Gen_A],
Prod_DE.[DE3-Gen_A+] AS [MaxDE3-Gen_A],
Prod_DE.[DE4-Gen_A+] AS [MaxDE4-Gen_A]
FROM Prod_DE
WHERE Prod_DE.Date=DMax("[Date]","Prod_DE&quot) AS b;

-----------------------------------

I have a question about your column names. Is the column name in the table actually 'DE1-Gen_A+'? Why the mixture of '-', '_' and '+' in the name? It makes the names difficult to read and confuses me considerably. For example, how can I know if I am dealing with the name of a column instead of a mathematic operation?

I suggest that if the query has another error, instead of immediately posting your error back in the forum that you try changing the query slightly. Experiment. Try to make it work on your own. That's one way to learn what works and what doesn't work. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[haj1503]

Hi Terry,

See you again....

Now i am very clear from you posting, my problem already solve no more messagge again.


Thanks again for you assistance i am appreciated.

Bless you.

Haj1503