NOW() & DATE() functions in web tables (1 Viewer)

0weavern · Nov 17, 2014

Hi All,

First post, so please be kind!

I've created a web access database which uses SharePoint to host. I need to add in an "overdue" field into a table but having some problems with the calculated field.

I know the DATE() and NOW() functions are not compatible with web tables but can't seem to find an alternative. Basically, I'm looking at creating a calculated field which says:

If (Sum (DATE()-[DueDate]) <0, "Overdue", "Not Overdue")

Any suggestions?

marlan · Nov 17, 2014

Hi, and welcome to the forum!

I'm not sour you can subtract dates, it isn't always accurate. try using DateDiff Function.

marlan · Nov 17, 2014

Oh, and I think your function should be IIF(), not if().

0weavern · Nov 17, 2014

Thanks marlan,

My main problem is the NOW() & DATE() functions don't work in web tables, so have nothing to DateDiff with. It needs to be a calculated field which looks at today's date and the due date and flags if it's overdue.

Hope that makes sense?

RainLover · Nov 18, 2014

I don't know what a web table is but this should work in a query.

Code:

If (DATE()-[DueDate]) <0, "Overdue", "Not Overdue")

Having written this I may be way off the mark, but give it a Burl.

marlan · Nov 18, 2014

Hi again,
I too have never seen web tables before... so I looked into them a bit:

1. Your condition is really IIf.

2. As you have stated, DATE() and NOW() functions are not compatible with web tables, neither is datediff. As RainLover has stated, you can use these functions in a query. If the query is on one table only, you can manipulate data though it.

Just create a query Selecting all data fields, and add to them the calculated fields.
If you want, you can change the name of the table, and give the query your original TableName. I've attached an example.

Good luck!

RainLover · Nov 19, 2014

marlan said:
Hi again,
I too have never seen web tables before... so I looked into them a bit:

1. Your condition is really IIf.

2. As you have stated, DATE() and NOW() functions are not compatible with web tables, neither is datediff. As RainLover has stated, you can use these functions in a query. If the query is on one table only, you can manipulate data though it.

Just create a query Selecting all data fields, and add to them the calculated fields.
If you want, you can change the name of the table, and give the query your original TableName. I've attached an example.

Good luck!

I believe you can use more than one table and include a field from each.

Just make sure your joins are correct. This is joins rather than relationships.

Sing out if you find me incorrect.

RainLover · Nov 19, 2014

I too have never seen web tables before... so I looked into them a bit:

I run Access 2003 and sill have no idea about "Web Tables"

Can you give me a quick explanation.

I am sure others would also be interested.

Thanking You.

spikepl · Nov 19, 2014

Think about the logic of this : date & now have no business in calculated fields in any tables (not TABLES!) , not just web tables.

Why is that? The question is left to the reader

marlan · Nov 20, 2014

RainLover said:
I run Access 2003 and sill have no idea about "Web Tables"

Can you give me a quick explanation.

I too have have just moved from Acc2003 to 2010, but still run mdb files in this environment.

I really mean a bit!...
what I saw in Acc2010 is that these tables offer calculated fields in the table level, you don't have to create a view (Query) for calculating data. I've seen this ability offered in other DB serevrs (SQL Server, MySQL, Oracle), not in Access.
It seems MS is now offering it in Access Web db - a template of an Access file for running in SharePoint, users interact the DB via web browser (her's where I stopped reading...).

My opinion: why bother a table with calculations? have tables store data, and views/Queries calculate.

All the best!

NOW() & DATE() functions in web tables (1 Viewer)

0weavern

New member

marlan

Registered User.

marlan

Registered User.

0weavern

New member

RainLover

VIP From a land downunder

marlan

Registered User.

Attachments

RainLover

VIP From a land downunder

RainLover

VIP From a land downunder

spikepl

Eledittingent Beliped

marlan

Registered User.

Similar threads

Users who are viewing this thread