Passthrough query

[BahBah]

Hi

My database is linked to SQL server using linked tables.

I am trying to move some of my queries to being passthrough queries for efficiency and speed. I am trying to do this primarily for combo boxes, and for reports.

Can passthrough queries deal with the access date() function ?

eg:

SELECT *
FROM Tbltest
WHERE (((Tbltest .dateMade)>="1/" & Month(Date()) & "/" & Year(Date()) And (Tbltest .dateMade)<=Date()))

Or do passthrough queries get converted to SQL automatically ? ie. getdate()

Thank you

 

[namliam]

Date should work just fine, same way the & works just fine...

One piece of unsolicited advice:
WHERE (((Tbltest .dateMade)>="1/" & Month(Date()) & "/" & Year(Date()) And (Tbltest .dateMade)<=Date()))

This is wrong for 2 reasons
1) Implicit conversion
"1/" & Month(Date()) & "/" & Year(Date())
Results in a text string and not a date, this is not good

2) US vs EU dates
"1/" & Month(Date()) & "/" & Year(Date())
You are clearly using a DD/MM/YYYY format, however this is not good. As primarily access can do strange things (depending on local settings) with dates.

Instead to resolve both issues and get the current first of the month do
Date() - Day(date()) + 1

Also a "between" syntax would be "more conventional" though in formance it shouldnt matter (much)

Finaly anything you fetch with the query is transported across the network, if you are looking for efficiency gains, Select * vs Select field1, field2, particularly if you are not (by far) selecting all fields, the specific select of field1, field2 will increase performance.

PS, Welcome to AWF

 

[DCrake]

Nailman,

Your comment on Select * v Select Field1, Field2 syntax stating that by selecting specific fields increases performance is contrary to my understanding and reading. By selecting all fields (Select *) actually increases performance as the engine does not have to differentiate between what is wanted against what is not. I remember a whiel ago I pointed this out to someone who was encountering slow network performance, when they used the select * the performace increased dramatically. Now I stand to be corrceted on this, this is only my belief.

David

 

[namliam]

Select * is slightly worse in my experience, as with * the database actually has to find the fields to display / fetch first.
The database still has to do "Select f1, f2, f3, f4, etc"

Also if your table has 50 fields but only 5 are used, select * will fetch all 50 and all data needs to travel the network. The speed may not be directly affected but your network ops will thank you for limiting the data usage.

Or atleast this is my understanding

 

[namliam]

I found these references while quick searching/googling... dont know if its still valid... but atleast I didnt just dream it up
http://coop.cis.uoguelph.ca/WTR_Repository/Posters/F2005/Robert_Bright.pdf

Not the best readable... but another here:
http://www.devshed.com/c/a/MySQL/SQL-Performance-and-Tuning-Considerations/3/

 

[BahBah]

Thank you for the welcome and replies

How would I get the last date of the month using a similar method ? or better still the first day of the next month ?

 

[namliam]

re: Date() - Day(date()) + 1

Sorry for the basic question but isn't that saying:

Date() - Day(date()) + 1

01/13/2009 - 13 + 1 ?

Correct

So therefor it returns the last day of the previous month ? Which is fine I'll just use a > rather than >=

2 times Incorrect,
01/13/2009 - 13 = 01/00/2009 = 12/31/2008
01/13/2009 - 13 + 1 = 01/01/2009

Date() - Day(date()) + 1
Is valid to find the first of the month...

Second wrong doing is only applicable if the date searched contains a time as well.
> [last day of previous month] is equal to ie. 12/31/2009 00:00:00
As a result anything on the 31st will also be selected, again assuming this field contains time too. If only date, then using > would be valid.

In addition, how would I get the last date of the month using a similar method ? or better still the first day of the next month ?

Use the (access) function Dateadd to add one month.

LOL I think you edited your post a little since I quoted it !!! LOL

 

[BahBah]

Something like this ?:

dateadd("m",1,Date()) - Day(date()) = last day of the current month

Yes I did edit my post as it dawned on me whilst I was rereading my post that I understood how the Date() - Day(date()) + 1 worked. Thanks

 

[namliam]

Something like this ?:

dateadd("m",1,Date()) - Day(date()) = last day of the current month

Something like that but not quite... This runs into problems when ever you are going from a long to short month and are on the "long side" i.e.

?dateadd("M",1,#31-10-2009#) - 31
10/30/2009
?dateadd("M",1,#31-01-2009#) - 31
1/28/2009

Instead go to the first of the month and move from there:
dateadd("M",1,Date() - Day(date()) + 1)
This will jump to the first of next month

dateadd("M",1,Date() - Day(date()) + 1) -1
This will jump to the last of this month

Keep in mind the difference between a date and a Date/time ....
Now() is always greater than Date() becuase date is 2009-10-26 00:00:00 and now is 2009-10-26 14:03:15

Edit:
Hence you can get into trouble when working with dates vs date/time's just make sure you know what you are working with when choosing to use the "last day of the month"

 

[BahBah]

Thank you for your very helpful replies

I used SELECT * just as an example to my issue, however I too also read somewhere, and it may have pertained only to MySQL (although I doubt it), that SELECT * was often faster than specifying field names. I always thought it was faster with field names too.

 

[BahBah]

Lastly, and I guess this is MSSQL specific, does BETWEEN include the start and end dates ?

So BETWEEN(01/01/2009 AND 01/30/2009) would include anything on the 1st and the 30th ?

And whilst I take your point re: US date formats, SQL appears to save in UK format. Is it access that has the problem and not SQL ? and if so, should I use american date formats on SELECTs ?

Thanks again

 

[namliam]

So BETWEEN #01/01/2009# AND #01/30/2009# would include anything on the 1st and the 30th ?

With the small correction, yes....

 

[BahBah]

Thanks for your help