Using SQL command within a formula field

[iamdavidlee]

hi

I am using Basic syntax to write the formula. I am basically going through some dates eg from 1st to 5th using a while loop. For each date i want to run a SQL count records query to get a total number of record for the period.

But i am not sure how to add an SQL query inside a formula field.

Would appreciate any help...

Thanks
David

 

[IdoMillet]

You can't embed SQL statements within formulas in Crystal.

You can look into creating SQL Expressions though. A thread in Forum 4 discussed the fact that you can use SELECT statements in SQL expressions (the statement must return a single value).

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[iamdavidlee]

hi

thanks for the reply... i am using the following code

Dim tempdate As Date
Dim x As Number
tempdate = {house.MovingInDate}
x =0
While tempdate <= {house.MovingOutDate}
tempdate=tempdate+1
wend
formula =x

What I need to do is to count how many ppl are in the house during each day for the looping period. I tried to use SQL count command there but it doesnt allow SELECT commands there. The number of ppl are in another table called rent. I am not sure how to use SQL Expressions, if i can use SQL Expressions i need to send the date and get back the count from rent table.

But i am not sure how to do this.

would really appreciate the help

thanks
david

 

[iamdavidlee]

hi

I have two tables.

House Rent table

HouseID RentID StarDate EndDate Amout
1 1 1/11/03 30/11/03 100.00
1 2 1/12/03 31/11/03 200.00

Basically this table has house rent details for different periods. From the above data there is a house which chrages a rent of 100 for October and 200 for December.

Occupant Table
RentID CusID CheckinDate CheckoutDate
1 5 1/11/03 15/12/03
1 6 1/11/03 30/11/03
1 7 20/12/03 31/12/03

This table has customers checkindate and checkoutdate. RentId is linked.

What I want in the report is to display the following details for each record in the House Rent table.

RentID CusID Amout

The problem is the amout needs to automatically calculated. The amout depends on how many ppl are sharing the house on everyday during period of stay.

Basically in the example data above, for the first month upto 15th two ppl are sharing the house so half the rent ($50) is divided between two ppl ($25 each). Then for the rest of that month there is only one peron so he should be allocated $50.

What I tried to do was for each House Rent record I went in a loop from House Rent Start Date to End Date for each cusomter to see which days he was in the house, this was working, I was also able to calculate amout per day but i am not sure how to check how many ppl occupied the house for a certain day so I can divided the days cost correctly between the occupants. This needs do be done while i am inside the loop check each day.

Would very much appreciate the HELP....

Thanks

David