PDA

View Full Version : Formula to check if database field exists



Seamus Conlon
30-Aug-2005, 11:59 AM
I am creating a report that uses an Access crosstab query as its
datasource. The query produces a table of sales per product
per month - products are the row headings and months are the
column headings. Depending on when it is run, there will be a
varying number of months.

So, within the report I need to be able to determine if a particular
month exists so that I can place it on the report. So, is it possible
to create a formula field that checks if a database field exists and
returns a value based on the outcome?

Thanks,
Seamus

David Martinko
30-Aug-2005, 12:28 PM
Why not create a Access table for each month and link it in the report? This
month table will never change and you will then be able to test whether
there are records for that month or not.

--
David Martinko
Redeemed Software
248-535-7495
RedeemedSoftware(SHIFT+2)Hotmail(PERIOD)com
www.redeemedsoftware.com

"Seamus Conlon" <seamus.conlon@eurokom.ie> wrote in message
news:eSXTxPYrFHA.1276@dacmail.dataaccess.com...
>I am creating a report that uses an Access crosstab query as its
> datasource. The query produces a table of sales per product
> per month - products are the row headings and months are the
> column headings. Depending on when it is run, there will be a
> varying number of months.
>
> So, within the report I need to be able to determine if a particular
> month exists so that I can place it on the report. So, is it possible
> to create a formula field that checks if a database field exists and
> returns a value based on the outcome?
>
> Thanks,
> Seamus
>

Seamus Conlon
31-Aug-2005, 03:21 AM
Sounds reasonable. But how do I test whether there are records
for a month or not? Do I have to creating a running total and then
see if it is zero or can it be done via a formula?

Seamus

"David Martinko" <RedeemedSoftware@Hotmail.com> wrote in message
news:Q3OnmgYrFHA.1276@dacmail.dataaccess.com...
> Why not create a Access table for each month and link it in the report?
> This month table will never change and you will then be able to test
> whether there are records for that month or not.
>
> --
> David Martinko
> Redeemed Software
> 248-535-7495
> RedeemedSoftware(SHIFT+2)Hotmail(PERIOD)com
> www.redeemedsoftware.com
>
> "Seamus Conlon" <seamus.conlon@eurokom.ie> wrote in message
> news:eSXTxPYrFHA.1276@dacmail.dataaccess.com...
>>I am creating a report that uses an Access crosstab query as its
>> datasource. The query produces a table of sales per product
>> per month - products are the row headings and months are the
>> column headings. Depending on when it is run, there will be a
>> varying number of months.
>>
>> So, within the report I need to be able to determine if a particular
>> month exists so that I can place it on the report. So, is it possible
>> to create a formula field that checks if a database field exists and
>> returns a value based on the outcome?
>>
>> Thanks,
>> Seamus
>>
>
>

David Martinko
31-Aug-2005, 06:22 AM
Close.

Yes, use a running total to count the number of records in the group based
on a date. Then use a formula to test the IsNull.

(IsNull({#CountOfRecords})) // Returns True/False

--
David Martinko
Redeemed Software
248-535-7495
RedeemedSoftware(SHIFT+2)Hotmail(PERIOD)com
www.redeemedsoftware.com

"Seamus Conlon" <seamus.conlon@eurokom.ie> wrote in message
news:zMQOKTgrFHA.632@dacmail.dataaccess.com...
> Sounds reasonable. But how do I test whether there are records
> for a month or not? Do I have to creating a running total and then
> see if it is zero or can it be done via a formula?
>
> Seamus
>
> "David Martinko" <RedeemedSoftware@Hotmail.com> wrote in message
> news:Q3OnmgYrFHA.1276@dacmail.dataaccess.com...
>> Why not create a Access table for each month and link it in the report?
>> This month table will never change and you will then be able to test
>> whether there are records for that month or not.
>>
>> --
>> David Martinko
>> Redeemed Software
>> 248-535-7495
>> RedeemedSoftware(SHIFT+2)Hotmail(PERIOD)com
>> www.redeemedsoftware.com
>>
>> "Seamus Conlon" <seamus.conlon@eurokom.ie> wrote in message
>> news:eSXTxPYrFHA.1276@dacmail.dataaccess.com...
>>>I am creating a report that uses an Access crosstab query as its
>>> datasource. The query produces a table of sales per product
>>> per month - products are the row headings and months are the
>>> column headings. Depending on when it is run, there will be a
>>> varying number of months.
>>>
>>> So, within the report I need to be able to determine if a particular
>>> month exists so that I can place it on the report. So, is it possible
>>> to create a formula field that checks if a database field exists and
>>> returns a value based on the outcome?
>>>
>>> Thanks,
>>> Seamus
>>>
>>
>>
>
>