Solved

Open feltered report by a date coming from a combo box

Posted on 2012-04-10
9
346 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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