SQL pass through query, date between form fields

[MushroomKing]

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?

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]

Try adding date delimiters

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]

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]

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]

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.

server.database.schema.table

In this case, 690P would be the Schema.

 

[Galaxiom]

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]

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]

Yes you just need to build the pass through correctly;
Create a pass through

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.

Set db = Currentdb

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

DoCmd.OpenQuery "YourPassThroughQuery"