Select on Date issue

[celauritz]

I have a table with four different columns that would figure date. They are all numerical...

Century (20)
Year (9)
Month (1)
Day (19)

I use the following date formula


NumberVar input := (1000000*{Century})+(10000*{Year})+(100*{Month})+({Day});
If input < 19720101 then Date (1972, 01, 01) else
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )

The problem is, if I put it in the select expert such as date in lastfullmonth it reads every record.

The only other thing I can think of is doing

year=9 and month=1

in february and then changing the report in March, etc.

I'm sure there is a better way to do this so I can put the info in the select expert and then the report will run for lastfullmonth, monthtodate, lastfullweek, or whatever I want, but I can't figure it out.

Any help?

 

[Madawc]

Use

DateTime (YYYY, MM, DD)

Substitute your own field values for the YYYY etc. This will assemble the date. You can then compare it to the current month and year, e.g.

Month(@MakeDate) = Month(Currentdate)
and 
Year(@MakeDate) = Year(Currentdate)

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could use:

date(({table.century}*100)+{table.year},{table.month},{table.day}) in lastfullmonth

This still probably won't pass to the SQL query.

-LB