Between dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I dont know exactly how to explain this but I'll give it a go. I apologize in
advance.

I have a form where I enter a date range and access goes to another
application and extracts claims where the claim date ranges matches the
"search criteria" in access. The "search criteria" can be a single date or a
date range. The other appication is basically a table full of claim
information like claim numbers and dates and whatever. This is what I'm
using:

FromDOS and ToDOS are the search criteria
fDOS and tDOS are the dates stored in the other application

'If FromDOS >= fDOS And ToDOS <= tDOS Then ...

The problem with that is if there are two claims (think two rows in that
table) in that date range, it fails and I get no data back.

'If fDOS >= FromDOS And tDOS <= ToDOS Then
This one solves that problem and retrieves both of those claims, problem
with that is, it retrieve any data if I only know one date. (If I only know
one date, the From and To dates are equal).

It just seems I can't have my cake and eat it too with this. Is there a
between statement or something that I can use? I have looked at this too long
to come up with a reasonable answer.
 
'If FromDOS >= fDOS And ToDOS <= tDOS Then ...

The problem with that is if there are two claims (think two rows in
that table) in that date range, it fails and I get no data back.
Click to expand...

You don't say how you are using this expression... I can see that it has
been commented out so I assume it's part of a VBA procedure is doing
something. I guess that sorting out the logic of the something is the key
here rather than the expression itself.

'If fDOS >= FromDOS And tDOS <= ToDOS Then
This one solves that problem and retrieves both of those claims,
Click to expand...

Sorry: I cannot for the life of me understand why these expressions
should operate differently. The only thing that could be (and I haven't
tested this) is that there is some implicit data type conversion going on
and going wrong -- but if you are comparing different data types with
your fingers crossed then that is just asking for trouble. What _exactly_
are fDOS and tDOS and FromDOS and ToDOS?
It just seems I can't have my cake and eat it too with this. Is there
a between statement or something that I can use? I have looked at this
too long to come up with a reasonable answer.
Click to expand...

Well, if you want to know how many records there are, then that is the
question you should ask of the database:

NumberOfClaims = DCount("*", "Claims", _
"fromDOS <= " & format(fDOS,jetDateFormat) & _
" AND toDOS <= " & format(tDOS, jetDateFormat) _
)

et cetera.

Hope that helps


Tim F
 
Thank you, Tim, for replying. And you are right. There is a date conversion
happening here. Its a big complicated mess, really. The FromDOS and ToDOS are
Date data type and the fDOS and tDOS are strings. The whole code is basically
doing a screen wipe of another application and everything coming from this
application is a string. I put a watch on it the other day and found out the
data type conversion I am using is actually converting that string into an
integer for some reason. I wondered the other day if that was part of my
problem, but no matter what I did, I could not get it to convert that string
into a date instead of an integer. I typed the integer value it returned
into excel and changed the format to a date and it turned out to be the
correct date, so I didn't worry about it. I figured it was something wrong
with my if statement.
 
The
FromDOS and ToDOS are Date data type
good

and the fDOS and tDOS are
strings.
Click to expand...

Bad. What type of strings? mmddyyyy or yyyy-mm-dd or what? Some formats
will parse into dates, some will not, and some will parse unreliably or
inconsistently.
The whole code is basically doing a screen wipe of another
application and everything coming from this application is a string. I
put a watch on it the other day and found out the data type conversion
I am using is actually converting that string into an integer for some
reason.
Click to expand...

What does the integer look like? If it's 20050511 then it's pretty easy
to manage. If it's 38848 then somehow it is being parsed into a date and
then coerced into a (date serial) number.

In either case, you need to get a grip on the formatting and parsing and
make sure the values are what you expect. For example,

fDosAsString = "20050511" ' yyyymmdd
fDosAsDate = DateSerial(CInt(Mid(fDosAsString,1,4)), _
CInt(Mid(fDosAsString,5,2)), _
CInt(Mid(fDosAsString,7,2)))


etc etc.

Now, comparisons like

If fDosAsDate = fromDOS Then
I typed the integer value it returned
into excel and changed the format to a date and it turned out to be
the correct date, so I didn't worry about it. I figured it was
something wrong with my if statement.
Click to expand...

What Excel does with type conversion is (a) magic and (b) not
neccessarily what you want in any given situation! I don't trust VBA to
do my coercions by default, and at least it's documented. It is not hard
to make your conversions explicit and is well worth it for the peace of
mind.

Hope that helps

Tim F
 
Back
Top