Formula Problem?

[wanderir]

Hello,
I'm trying to display all of the bed spaces in the "booked bed spaces" table(rmgt_t_room_person.ck_bed_space) for a parameter(?term) as well as all the rooms that are unbooked for the same period. (rmgt_t_room_configs.pk_bed_space) Contains all bed spaces. The "all bed spaces" table does not have a way to link to a date range and does not have a field that indicates whether it is booked or not.

(?term)= 8 charecter datetime field (rmgt_t_room_person.move_in_date)This field indicate the start of a term.

The formula I'm using is:

if {rmgt_t_Room_Configs.ck_Bed_Space}<>{rmgt_t_Rooms.fk_Room_No}
and isnull({pple_t_Person.ix_Last_Name}) or{rmgt_t_Room_Person.ck_Move_In_Date}={?Term}
then right({rmgt_t_Room_Configs.ck_Bed_Space},6)

I'm leaving out the rooms and just including the bedspaces which is what the first part of the formula is for.

I was able to do a basic version of this in Access by building a query that inlcuded only booked bedspaces that had a rmgt_t_Room_Person.ck_Move_In_Date of 01/01/2001
then I created a second query that left joined the &quot;all bed spaces&quot; table with the first query and this worked just fine. Well except for not having a parameter.

Thanks in advance!
Ed Colbeth

 

[wanderir]

I've been working on this for a couple of weeks. I'm new to Crystal and don't know programming. Any help would be greatly appreciated!

Thanks,

Ed Colbeth

 

[FowlerConsulting]

Why not bind the report to the working Access Query?
You do not have to use the tables...


Steven Fowler
steven_m_fowler@yahoo.com

http://www.fowlerconsulting.com

 

[wanderir]

Steve,
Great idea! But how do I pass the date parameter {?term} to Access?

Thanks,
Ed Colbeth

 

[FowlerConsulting]

Is it Access 2000?
Steven Fowler
steven_m_fowler@yahoo.com

http://www.fowlerconsulting.com

 

[wanderir]

Sorry,
I'm using Crystal Reports 8.0.1.0 and Access 97.

Thanks,
Ed Colbeth

 

[FowlerConsulting]

Still use the formula back in the query, but you will have to fetch all the records and then exclude by Move_In_Date at the report level.

Does that help?
Steven Fowler
steven_m_fowler@yahoo.com

http://www.fowlerconsulting.com

 

[wanderir]

I don't think that will work.....

WtrWtrBeds:
SELECT rmgt_t_Room_Person.ck_Bed_Space, rmgt_t_Room_Person.ck_Move_In_Date
FROM rmgt_t_Room_Person
WHERE (((rmgt_t_Room_Person.ck_Move_In_Date)=#1/1/2001#));

AllBds:
SELECT rmgt_t_Room_Configs.ck_Bed_Space
FROM rmgt_t_Room_Configs LEFT JOIN WtrQtrBeds ON rmgt_t_Room_Configs.ck_Bed_Space = WtrQtrBeds.ck_Bed_Space;

As you can see I'm using a move_in_date of 1/1/2001 in place of the parameter {?term}

Is there a way to exclude move_in_date via a parameter?

Thanks again for your time!

Ed Colbeth

 

[FowlerConsulting]

Again,
you will have to do half the WHERE in the QUERY and the other half in the report. The report have will include the parameter {?term}

EX:
if {rmgt_t_Room_Configs.ck_Bed_Space}<>{rmgt_t_Rooms.fk_Room_No}
and isnull({pple_t_Person.ix_Last_Name}) or{rmgt_t_Room_Person.ck_Move_In_Date}={?Term}
then right({rmgt_t_Room_Configs.ck_Bed_Space},6)

Query:
if {rmgt_t_Room_Configs.ck_Bed_Space}<>{rmgt_t_Rooms.fk_Room_No}
and isnull({pple_t_Person.ix_Last_Name})

Report:
{rmgt_t_Room_Person.ck_Move_In_Date}={?Term}
then right({rmgt_t_Room_Configs.ck_Bed_Space},6)

I am not sure I can help you any more than this with out phone, or on site consulting... sorry.
Steven Fowler
steven_m_fowler@yahoo.com

http://www.fowlerconsulting.com

 

[wanderir]

Steve,
Thanks again for your time!

Ed Colbeth