is there an easier way to do this??? 2

[smiley0q0]

i was given the task to clean this database up and i found this formula in the criteria of a query, unfortunatly i have not been able to decifer this one, i am fairly new to access, but i am learning. can anyone tell me what this is doing and if there is a simpler way of saying it?

Between Switch(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1<10,&quot;00&quot; & Trim(Str(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1)),([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1<100,&quot;0&quot; & Trim(Str(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1)),True,Trim(Str(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1))) And Switch(([End Date]-DateSerial(Year([End Date]),1,1))+1<10,&quot;00&quot; & Trim(Str(([End Date]-DateSerial(Year([End Date]),1,1))+1)),([End Date]-DateSerial(Year([End Date]),1,1))+1<100,&quot;0&quot; & Trim(Str(([End Date]-DateSerial(Year([End Date]),1,1))+1)),True,Trim(Str(([End Date]-DateSerial(Year([End Date]),1,1))+1)))

here is another one very similar:

Between (IIf(([Begin Date]-DateSerial(Year([BeginDate]),1,1))+1<10,&quot;00&quot;&Trim(Str(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1)),IIf(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1<100,&quot;0&quot;&Trim(Str(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1)),Trim(Str(([Begin Date]-DateSerial(Year([Begin Date]),1,1))+1)))))And(IIf(([End Date]-DateSerial(Year([End Date]),1,1))+1<10,&quot;00&quot;&Trim(Str(([End Date]-DateSerial(Year([End Date]),1,1))+1)),IIf(([End Date]-DateSerial(Year([End Date]),1,1))+1<100,&quot;0&quot;&Trim(Str(([End Date]-DateSerial(Year([End Date]),1,1))+1)),Trim(Str(([End Date]-DateSerial(Year([End Date]),1,1))+1)))))

if you need more specific info please let me know...
thank you in advance

 

[ghubbell]

Hi Smiley!
Personally I would need more info about the query, but basically it is a date comparison &quot;Between date and another date&quot; using a Switch function and according to MS, it &quot;Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.&quot;
This is nasty! If it works don't touch it!

The second one is almost legible, again between date and another date but I'll give it a ditto on my last comment. Perhaps someone with better eye sight than I can pick it apart for you... Anyone? Gord
ghubbell@total.net

 

[dneufarth]

Looks as if both formulas are doing an inclusion between two computed 3 character strings that are right justified and left padded with zeroes (ie 003, 069, 355, etc.).

The first string is the difference in days between [Begin Date] and the first day of the same year contained in [Begin Date] (ie 3/1/2000 - 1/1/2000).

Second string is same, but between [End Date] and first day of the same year contained in [End Date] (ie 3/1/2001 - 1/1/2001).

One formula uses a Switch function to create the 3 character strings and the other is using nested IFs to achieve the same thing.

Does that make sense as far as the query criteria?


Dave

 

[dneufarth]

something like this may be simpler:

BETWEEN Format(([Begin Date]-DateSerial(Year([Begin Date]),1,1)+1),&quot;000&quot AND Format(([End Date]-DateSerial(Year([End Date]),1,1)+1),&quot;000&quot


Dave

 

[smiley0q0]

Thanks guys... i think i am starting to undertand it. i will try and see if your code works neufarth. gord, i agree with you 100% this is NASTY!!!
neufarth can you tell me what it means or why he is using &quot;<10&quot; and &quot;<100&quot; in there, that's where i got lost.
thanks again

 

[MichaelRed]

I'm pretty sure this is the same as &quot;neufarth's&quot; soloution,
however it may be slightly easier to &quot;DeCode&quot;/understand. Obviously either is a lot shorter than the original.

Public Function basDateTests(BeginDate As Date, EndDate As Date, DOY As String) As Boolean

    Dim BOD As String * 3
    Dim EOD As String * 3

    BOD = Right("000" & Trim(Str(Day(BeginDate))), 3)
    EOD = Right("000" & Trim(Str(Day(EndDate))), 3)

    If (DOY >= BOY And DOY <= EOD) Then
        basDateTest = True
    End If

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rafe]

SQL vs VBA pick your poison...

smiliey, look at the 1st paragraph of neufarth's post. what the 1st programmer was doing was padding &quot;000&quot;s to the front of a day number, IN A VERY ROUND ABOUT WAY! that's all. so < 10 s/he put 2 &quot;00&quot; in front, less than 100 s/he puts &quot;0&quot; in front.

when i see code like that i
1) put it into my editor and manually format it so that the program's logic becomes clear.
2) i add comments that translate the code into english.
3) see if i can make improvements/simplify... but i make sure i can go to a backup if i'm wrong

 

[smiley0q0]

WOW thank you guys, i have been trying to translate this for a while. i used neufarth's code and it worked great!!!
neufarth's one formula replaced both of those huge nasty things.
thank you rafe for your detail, once i read yours i understood it a little better,
sorry michaelred i'm not quite into vba yet, but i'm getting there.