Date Query in Access Project - SQL

[msamhouri]

I am working in access project, so the backend of the system is SQL, I tried to build a query to retrieve the data based on the date which shows in text box. I wrote in the query the following
Like [forms]![Main]![Text49]
but its not working, can you help me with this formula please?

Thank you in advance for your help

[zmbd]

You need ALOT more detail here...
The simple.... it's not working... does not help us help you.
Normally, full solutions are not provided unless prior work is shown... cuts down on the homework questions.

Like [forms]![Main]![Text49]
poorly formed string.
if the [Text49] is supposed to be a date you'll need the hash deliminators also you're missing the "&" to tie things together and the remaining SQL is missing as you have no ";" to demark the end of the SQL.


Please post the entire SQL or enough of the VBA so that we can tell what you're doing - remember the <CODE/> button...

Please describe what exactly you are expecting for the data

Please describe exactly what you are getting back... including any errors.

[msamhouri]

Criteria
okay sir, i have a form called Main and there is a textbox in this form called Text49, this form used to determine the date, so when you put the date and click enter, a query should shows the results of accounts matching the date in the text box, i used to do it in access without problem, but in access project is not working???

i tried to put this formula in the criteria (Like [forms]![Main]![Text49]) like i used to do in regular access but it’s not working!!

I am not expert with coding specially SQL??

is it okay or you need more explanations??

Thank you

[msamhouri]

sorry but I spent all day long working at this query and its still not working?

[zmbd]

You have not provided the requested information.

No one will be able to help you until you do so, not because we don't want to, because you haven't given us anything to work with! Doesn't matter how long you've worked on the problem if you don't show us the work!

[msamhouri]

Hello everybody, according to the last email, I wanna put the code, can you help me to put formula when I determine specific date, the query shows the data according to this date??

I tried this formula and it not Working (Like [forms]![Main]![Text49]_)

the SQL code is :

SELECT
   TOP 100 PERCENT 
   dbo.[1_Account_Types].AcctTypeID, 
   dbo.[1_Account_Types].AccTypeName, 
   dbo.[2_Account_Group].AcctGroupName,  
   SUM(dbo.Acct_DataT.Balance) AS ASSTAMOUNT, 
   dbo.Acct_DataT.TransDate 
FROM dbo.[3_Account_SubGroup] 
   INNER JOIN dbo.[4_Account_Level4] 
      ON dbo.[3_Account_SubGroup].AcctSubGroup = 
            dbo.[4_Account_Level4].AccSupGoup 
      INNER JOIN dbo.Acct_DataT 
      ON dbo.[4_Account_Level4].AccountNo = 
            dbo.Acct_DataT.AccountNo 
   RIGHT OUTER JOIN dbo.[1_Account_Types]
      INNER JOIN dbo.[2_Account_Group] 
         ON dbo.[1_Account_Types].AccTypeName = 
            dbo.[2_Account_Group].AccTypeName 
         ON dbo.[3_Account_SubGroup].AcctGroupName = 
            dbo.[2_Account_Group].AcctGroupName 
GROUP BY 
   dbo.[1_Account_Types].AccTypeName, 
   dbo.[1_Account_Types].AcctTypeID, 
   dbo.[2_Account_Group].AcctGroupName,
   dbo.[2_Account_Group].AcctGroupID, 
   dbo.[2_Account_Group].NYAccount_1, 
   dbo.Acct_DataT.TransDate 
HAVING
   (dbo.[2_Account_Group].NYAccount_1 = 1) 
   AND (dbo.[1_Account_Types].AcctTypeID = 1) 
   OR (dbo.[1_Account_Types].AcctTypeID = 4) 
ORDER BY 
   dbo.[1_Account_Types].AcctTypeID, 
   dbo.[2_Account_Group].AcctGroupID

[NeoPa]

I think you need to look at the SQL Server side for formatting your SQL. I'm guessing, though you don't actually say, that you are not using linked tables, but pass-thru queries. Formatting of dates in SQL varies a lot between Jet (Access) SQL and SQL Server's Transact-SQL.

You may find some help in Literal DateTimes and Their Delimiters (#), but remember this is not the format required for T-SQL. For that you should check out T-SQL: Date and Time Data Types. i give both as i'm still very unclear about what situation you're in.

Probably the most important point to learn from this thread though, is that you need to present a proper question if you want anyone to help you with an answer. It can be difficult, I know, but without it your threads will just form part of the dross that makes up such a large part of what's available on the web, and waste everybody's time.

[NeoPa]

Please remember to use the [ CODE ] tags when posting code.

As for your SQL, the strangest thing (among many strange things) is that there is nothing there that even tries to filter by any form of date value or literal. This is just confusing.

[zmbd]

one of those pass-thru-queries again...

You see... pass thru queries do EXACTLY that... they pass the SQL "as is" to the backend server. Because the form is not open on the backend server it has no idea what value the parameter is refering to on the form.

This is in ADDITION to the other issues with the posted SQL as noted by NeoPa.

I couldn't tell from OP what was being done... pass thru, wrong SQL form for Access, VBA call with ill-formed strings, or if the Knights-of-NEE had taken ahold of OP's hand.

There is a solution for pass thru queries - you have to have them resolved: http://support.microsoft.com/kb/131534

That'll be a small Icecream Sundae please :)