?
Solved

Problem in a filter with to dates using  Between condition

Posted on 2006-05-05
19
Medium Priority
?
243 Views
Last Modified: 2008-02-01
Hi,

I'm building a report in access that should give some statistics of the records in a certain table. For that, i'm using Dcount to return the number of records that matches some criteria.

I'm uisn this two conditions in the Control Source property of the field:

Field 1 -> =DCount("Origem";"Investigacao";" Data Between #" & [Data1] & "# And #" & [Data2] & "#")
Field 2 -> =DCount("Origem";"Investigacao";("[Investigacao.Estado] Like 'Investigação' And  [Investigacao.Origem]_ Like 'Aprovação Contrapartes' And Data Between #" & [Data1] & "# And #" & [Data2] & "#"))

[Data1]  and  [Data2]  are inserted in a form and recorded in a specific table to this report

This two conditions returns exactly what is expected if i am using dates between 01-01-2005 and 30-04-2006. My problem beguns when the dates are like 01-05-2006 to 05-05-2006 (present day).

I have already tried to write the conditions replacing [Data1] for 01-05-2006 and still have no results.

If the conditions is changed to "Data like '01-05-2006'" returns the results correctly.

Thanks in advance.
 
0
Comment
Question by:mjmoliveira
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16613925
try converting the format of  the dates like this

Field 1 -> =DCount("Origem";"Investigacao";"Cdate(Format([Data],'yyyymmdd')) Between #" & Cdate(Format([Data1],'yyyymmdd')) & "# And #" & Cdate(Format([Data2],'yyyymmdd')) & "#")
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16614092
Looks to me your datefield has been defined as a textstring instead of a real date.
Can you open the table Investigacao and check the Data field for it's fieldtype ?

Nic;o)
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 750 total points
ID: 16614106
Will that execute?
CDate needs some kind of date part separator character.  Or it might return jibberish (converting from a number directly)
So CDate('yyyymmdd') can't be interpreted.

What about

Field 1 -> =DCount("Origem";"Investigacao";" [Data] Between #" & Format([Data1], 'yyyy-mm-dd') & "# And #" & Format([Data2], 'yyyy-mm-dd') & "#")

To make the date unambiguous.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16614115
(Indeed - as Nico points out, assuming [Data] is a date type field).
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16614129
When it's indeed a text string use:
=DCount("Origem";"Investigacao";"Cdate([Data]) Between #" & Cdate([Data1]) & "# And #" & Cdate([Data2]) & "#")
but it would be better to change the report.
I guess your report needs to have the same date range and by adding a field in your query like:

Select IIF([Investigacao.Estado] Like 'Investigação' And  [Investigacao.Origem]_ Like 'Aprovação Contrapartes',1,0) as Hit, ...
then you get t a 1 or 0 for a hit. This can be summed to get the value for field 2 and a simple recordcount (or a field with a sequence number) can be used for the total rows selected.

Getting the idea ?

Nic;o)
0
 

Author Comment

by:mjmoliveira
ID: 16614346
Hi,

First, related to nico question, the field  Data in the table Investigacao is:

Data Type: Date/Time
Format: Short Date

I will now try all the other suggestions and give you feddback as soon as possible.

Thanks once again

mjmoliveira
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16614438
Hmm, still best to checkout my changed report query proposal when the report is indeed also "between" dates.

Otherwise try the data / format solution proposed by LPurvis

Nic;o)
0
 

Author Comment

by:mjmoliveira
ID: 16616357
Hi,

I am starting to try LPurvis sugestion, using the expression in the report field control source and Access gives me a error message saing that the expression contains invalid sintax.

Then i have changed the Format to FormatDateTime in the expression:

=DCount("Origem";"Investigacao";"[Data] Between #" & FormatDateTime("[Data1]";'yyyy-mm-dd') & "# And #" & FormatDateTime("[data2]";'yyyy-mm-dd') & "#")

Access acepts this message, but the report retuns  - #Error#

The fild date is formated as Short Date, is there a confict between the formats ?

Thanks

mjmoliveira
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16616385
Try:

=DCount("Origem";"Investigacao";"[Data] Between #" & FormatDateTime(Me.[Data1],'yyyy-mm-dd') & "# And #" & FormatDateTime(Me.[data2],'yyyy-mm-dd') & "#")

This does assume that [data1] and [data2] are on your form.

Nic;o)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16616394
Why did you change the function away from Format? :-S
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16616419
Because it was refusing to accept the expression?
That seems to point to some other (greater?) problem.

Is this an expression in the controlsource of a textbox?
(You'll not be able to use the Me predicate there :-)
0
 

Author Comment

by:mjmoliveira
ID: 16616423
Nico,

you have suggested to change the report query, but i am using no query. My report is like a chess board and every field should return the number of records for two different criteria between to dates. Like this :

=DCount("Origem";"Investigacao";("[Investigacao.Estado] Like 'Investigação' And  [Investigacao.Origem] Like 'Aprovação Contrapartes' And Data Between #" & [Data1] & "# And #" & [Data2] & "#"))


I am using this expression in every field Control Source and not in a query, and for dates before 30-04-2006 it works perfectly. The problem starts when the date change to 01-05-2006...

My report have about 60 fields using the same expression with diferent criteria for  [Investigacao.Estado] and for [Investigacao.Origem] but with the same dates.

Do you think that using a query is a better option?

Thanks once again.

mjmoliveira
0
 

Author Comment

by:mjmoliveira
ID: 16616466
You are right LPurvis, this is an expression that i am using in the controlsource of a textbox.

And i have just change your expression from format to FormatDateTime because access keeps telling me that the expression have invalid sintax... :-(

With the change Access accepted, but return the error...



0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16616617
FormatDateTime is an entirely different beast.

If you truly have a "chessboard" of these then your report will surely run like a dog anyway - no?

Nico's right - if at all and in *any* way possible, it should really be done with a query.
If not then limit your report as much as you can.

As for the problem in hand...
What do you get in a textbox with just

=Format([Data1],"yyyy-mm-dd")


Do you actually have controls (textboxes) on the report that hold the values for Data1 and Data2 (and are named the same too?)
If not - add some that are.  (You can set their visible property to No if required).
0
 

Author Comment

by:mjmoliveira
ID: 16616778
In fact the report runs perfectly well for dates before 30-04-2006, and i have tested with records since 01-01-2005 to 30-04-2006.

I am using [Data1] and [Data2] (visible) to inform the user of the period of the report analisis and those work for every date.

I have tried tried to change the PC date (change to 2007) and for dates like 01-06-2006 it works... seems like the month 05 is the problem.

Everytime that i use a date in month 05 the report retur 0 instead of real number of records...

0
 

Author Comment

by:mjmoliveira
ID: 16617072
LPurvis, i am trying to insert

=Format([Data1],"yyyy-mm-dd")

in the controlsource of the textbox and access sends a message saying that the expression contains invalid sintax...

Like this, i can't get a result from this condition...

Is there any other problem with my report or with my access ??



For now i am going for my weekend, and next monday i will continue with this adventure....

Have a nice weekend and thank you all for your efforts to help me.

mjmoliveira
 
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 750 total points
ID: 16617305
Hmm, surely this report requires a different approach when I see your statement:
>My report have about 60 fields using the same expression with diferent criteria for  [Investigacao.Estado] and for [Investigacao.Origem] but with the same dates.

This is hard to maintain and I would record the different combinations of [Investigacao.Estado] and [Investigacao.Origem] in a separate table like:
CountFor [Investigacao.Estado]     [Investigacao.Origem]    
    1          'Investigação'                'Aprovação Contrapartes'
    2          'A.'                               'X.'
    2          'B.'                               'Z.'
Using the same value for the [CountFor] will allow to count different combinations into one field when needed.

Now you can join this table with the original table and add the WHERE for the [Data] field.
By making this a GroupBy query you can get all 60 counts in different records and use a columnar report to get the fields "compact" in multiple columns reported.

Getting the idea and see that this is (besides a lot faster) also a more flexible approach ?

Nic;o)
0
 

Author Comment

by:mjmoliveira
ID: 16629716
Hi guys

For start i wanto to thanks you all for your eforts to help me.

I have solve the problem for now. The problem was an inversion of dates made by Access. To solve this i have just invert the way the user enter dates to filter the report. I have changed from DD-MM-YYYY to YYYY-MM-DD, this way Access use the dates without inversions.

I have accomplish this using your advices for using date format.

I will also try to follow Nico advice and remodel the report using a diferrent table for the different combinations, for the next version. But for the moment, since the users have some urgency i will use this one.

At the end, you both have helped me, but now i have some doubts how to close this issue and give you guys this question points.

Can you give me some advice ?

Shoul i split the points between you two??

Thanks once again for your help.

mjmoliveira
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16629740
Glad we could help mjmoliveira, but I never advise in the way to assign points as that's up to you.
For splitting there's a link at the bottom of this question >Split points

Success with your application !

Nic;o)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

839 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