Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Open feltered report by a date coming from a combo box

Posted on 2012-04-10
9
Medium Priority
?
348 Views
Last Modified: 2012-04-12
Hi:
I had try plenty of such statements below
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Me.cboFeedingDate.Column(2) & "#", acWindowNormal
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Format(Me.cboFeedingDate.Column(2), "dd/mm/yy") & "#", acWindowNormal
 DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= " & Format(Me.cboFeedingDate.Column(2), "dd/mm/yy"), acWindowNormal
So and so ….
But I couldn’t get the right solution, always I get 0 records.
I have to choose a specific date from a combobox, of row source is " SELECT [tblTransactions].[ID], [tblTransactions].[TransType], [tblTransactions].[TransDate] FROM tblTransactions WHERE [tblTransactions].[TransType]=1 ORDER BY [TransType]; "
And my query behind the report is " SELECT tblTransactions.ID, tblItems.ItemName, tblTransactions.TransDate, tblTransactionsItems.Quantity_Retail, tblTransactions.TransType, tblSuppliers.Supplier FROM ((tblTransactions LEFT JOIN tblTransactionsItems ON tblTransactions.ID = tblTransactionsItems.TransID) LEFT JOIN tblItems ON tblTransactionsItems.ItemID = tblItems.ID) LEFT JOIN tblSuppliers ON tblTransactions.SupplierID = tblSuppliers.SupplierID WHERE (((tblTransactions.TransType)=1)) ORDER BY tblSuppliers.Supplier;"
I always display the generated (Me.cboFeedingDate.Column(2)), in msgbox and it shows appropriate date, but I don’t understand what is the problem .
Note that my database is Gregorian date and not hijri date.
Please help
Query-Behined-my-Report.JPG
My-report-results.JPG
0
Comment
Question by:Mohammad Alsolaiman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829973
Try it like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" &  "#" & Me.cboFeedingDate.Column(2) & "#"

Post a sample of the DB that has this issue

Are you quite sure that you have records matching that date?

Are you quite sure that none of your dates actually contain a "Time" component that is simply hidden by formatting?

What happens  if you just put a textbox on the form and enter the date there, then do something like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" & "#" & Me.txtFeedingDate & "#"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37830008
...it also might have something to do with your regional settings too...
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37830865
here is a sample of my db
please try to open form "frmReportsMenue"
then choose report No# 3 from the lest "because of the list is in arabic"
good luck
-Inventory-be2.accdb
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37830907
First try the suggestion i posted, the report back.

<Try it like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" &  "#" & Me.cboFeedingDate.Column(2) & "#">

<What happens  if you just put a textbox on the form and enter the date there, then do something like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" & "#" & Me.txtFeedingDate & "#">
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37830950
i had try the first one
but the text i'll try it at night (In sha'a allah)
thanks a lot : boag2000
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37835930
I had try all of them
"DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= " & Me.txtSupplier, acWindowNormal" dose not work  
"DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Me.txtSupplier & "#", acWindowNormal" this is work but i had to enter the date starting from year like this "2012/04/07" , but if i start backward  "07/04/2012" it will not work.
 as long i need it to be combo box , i'll try to backward  the selected item and see what will happen.
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37836919
Yes, OK
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Right(Me.cboFeedingDate.Column(2), 4) & "/" & Mid(Me.cboFeedingDate.Column(2), 4, 2) & "/" & Left(Me.cboFeedingDate.Column(2), 2) & "#", acWindowNormal
0
 

Author Closing Comment

by:Mohammad Alsolaiman
ID: 37836975
thanks a lot boag2000
here is the code when revers the date
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Right(Me.cboFeedingDate.Column(2), 4) & "/" & Mid(Me.cboFeedingDate.Column(2), 4, 2) & "/" & Left(Me.cboFeedingDate.Column(2), 2) & "#", acWindowNormal
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37838170
This is why a Sample database is Always helpful...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question