SQL pass through query, date between form fields (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 07:00
Joined
Jun 7, 2018
Messages
100
Hi everyone!

I'd like to ask for some help I can't seem to overcome. It's actually fairly simple but I have NO clue what i'm doing anymore.

I have an ODBC connect with a pass through query.
The query works FINE!
Until i try to filter between the date between 2 form fields.
What is going wrong here?


Code:
SELECT 
ORDER_ID
,STATUS
,TO_CHAR(Ship_By_Date, 'YYYY-MM-DD') AS ShipByDate
FROM 690P.MV_HEADER
WHERE 
[B]CLIENT_ID='TMM' And STATUS='Picked' And (((Ship_By_Date) Between [Forms]![main_form]![startdate] And [Forms]![main_form]![enddate]))
[/B]GROUP BY ORDER_ID, STATUS ,TO_CHAR(Ship_By_Date, 'YYYY-MM-DD')


Thanks in advance guys!
Appreciate it.
 

isladogs

MVP / VIP
Local time
Today, 14:00
Joined
Jan 14, 2017
Messages
18,186
Try adding date delimiters

Code:
SELECT 
ORDER_ID
,STATUS
,TO_CHAR(Ship_By_Date, 'YYYY-MM-DD') AS ShipByDate
FROM 690P.MV_HEADER
WHERE 
CLIENT_ID='TMM' And STATUS='Picked' And (((Ship_By_Date) Between #" & [Forms]![main_form]![startdate] & "# And #" & [Forms]![main_form]![enddate] & "#))
GROUP BY ORDER_ID, STATUS ,TO_CHAR(Ship_By_Date, 'YYYY-MM-DD')

The FROM line looks wrong. What is the name of your table?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:00
Joined
Jan 20, 2009
Messages
12,849
A Passthrough query is sent directly to the server. Access objects shuch as forms have no scope there.

Passthrough queries must use the syntax of the server. The # is the Access date delimiter. In other systems such as SQLServer, it is usually the single quote.
 

isladogs

MVP / VIP
Local time
Today, 14:00
Joined
Jan 14, 2017
Messages
18,186
Oops. Thanks for correction. Forgot it was a pass through query even though it was clearly described as such


Sent from my iPhone using Tapatalk
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:00
Joined
Jan 20, 2009
Messages
12,849
Code:
FROM 690P.MV_HEADER

The FROM line looks wrong. What is the name of your table?

A table can optionally have up to four parts in its name when linked servers are used.

Code:
server.database.schema.table

In this case, 690P would be the Schema.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:00
Joined
Jan 20, 2009
Messages
12,849
Oops. Thanks for correction. Forgot it was a pass through query even though it was clearly described as such

Sent from my iPhone using Tapatalk

Easy to miss stuff working from a phone.
 

MushroomKing

Registered User.
Local time
Today, 07:00
Joined
Jun 7, 2018
Messages
100
Hey guys! Thanks for all the answers.

Indeed the table looks a bit off because I didn't want to put the full name because of some security issues.

Anyway the table is fine. Everything works fine, until the BETWEEN form dates need to be put in.

So it's not possible to take values from these fields in a pass through query?

Cheers
 

Minty

AWF VIP
Local time
Today, 14:00
Joined
Jul 26, 2013
Messages
10,354
Yes you just need to build the pass through correctly;
Create a pass through
Code:
Dim sSql as String
Dim db as Database

sSql = "SELECT ORDER_ID ,STATUS ,TO_CHAR(Ship_By_Date, 'YYYY-MM-DD') AS ShipByDate FROM 690P.MV_HEADER "
sSql = sSql & "WHERE CLIENT_ID='TMM' And STATUS='Picked' And (((Ship_By_Date) Between "
sSql = sSql & "'" & Format([Forms]![main_form]![startdate],"yyyy-mm-dd") & "' And '" Format([Forms]![main_form]![enddate],"yyyy-mm-dd") & "' "
sSql = sSql & "GROUP BY ORDER_ID, STATUS ,TO_CHAR(Ship_By_Date, 'YYYY-MM-DD') "
Then use this as the basis for your pass through.
Code:
Set db = Currentdb

With db.QueryDefs("YourPassThroughQuery")  ' Make sure this is saved to return results 
       .SQL = sSql
End With 

DoCmd.OpenQuery "YourPassThroughQuery"
 

Users who are viewing this thread

Top Bottom