Discuss "Writing Expressions" in the Microsoft Access forum.

I am having trouble writing Expressions. I am currently trying to use DFirst. I am trying to figure out what I would include in the expression argument, The Domain argument and the criteria. I know this is vague so Ill explain further. I simply want the first date of a field of dates. If I could also request of who ever answers a good definition of expression domain and criteria. I thought I understood what they meant but I cannot figure out why they are in this function. I assumed that I would write dfirst([THE FIELD]) and it would just give me the first date. Any help or direction? Tanks:<---- ME

First of all, domain aggregate functions should only be used if you cannot use a query handily. They are inheritantly slow, which is why you want to try to stay away from them as much as possible.

Ok Let's start with the basics. Let's say we have a table that has 2 fields. A data field and a number field.

Table1
MyDate = Date/Time
MyNum = Number

And the following data

MyDate -- MyNum
1/1/2008 -- 2
2/1/2008 -- 1
3/1/2008 -- 1
3/1/2008 -- 2

Ok, so let's start with the basics. Let's just pull up the first record from a query in design view:

Expr1: Dfirst("[MyDate]", "Table1")

The output will be 1/1/2008

This will pull up the first record. (Remember dfirst and dlast merely pulls up the first and last records in no particular sort order. If you want highest or lowest values then use dmin and dmax)

Now, let's add some criteria. Let's pull up the first record where MyNum = 1:

Expr2: Dfirst("[MyDate]", "Table1", "[MyNum] = 1")

The output will be: 2/1/2008

Finally, let's do this from a variable instead of a constant number in the criteria:

Expr3: Dfirst("[MyDate]", "Table1", "[MyNum] = " & [AnotherExpr] & "")

Thank you so much that was very well writen. While we are on domain functions and I have read you say that before about them being slow; I am trying to figure out how i would get around using them. I now realized that I am using data in a table that can be calculated so Ill change that but You might see my delema.

TableCheckingAccount
DateFrom
DateTo
Income
Expenses

I want to periodize (dont know if that's a word) income and expenses. I just realized that I can just have Dateto as my field and calculated the datefrom field if need be but eventually i want to query my data in this manner:

last 180 days
last 13 periods
etc.

with only dateTo I will never be able to define that first period example:

6/1/08 - 6/30/08 <--- 6/1/08 cannot be defined from a previous period
7/1/08 - 7/31/08 That does not exist since it is the first
8/1/08 - 8/31/08

I imagine that once the user starts using the program they can establish a constant (First day) that will be saved and then can be calculated against but I dont really know how to do that. This would also alleviate the need for a DLast - Dfirst equation to quatify "how many days" are in the period. am I right in my thinking?

Anyone want to tackle what I wrote in the last thread of this post? I am not sure how to make a constant (the first date of entry) so that all records after that can enter only end of period Example to be clear:

8/2 is first day

Period one (record one) 8/2 - 8/10 <---- 8/10 is recorded
Period two (R 2) 8/11-8/19 <---- 8/19 is recorded
Period 3 (R 3) 8/20-8/23 <---- 8/23 is recorded

As you can see I can mathematically get the beginning date of all periods except the first by simply going to the previous record and adding 1 to the day. But my problem is with that first period. I assume you would ask the user to enter a day and store it in a constant (the first day date) and then subtract that first record from there. This would eliminate a Start date Column and the need to use aggregate functions. Am I thinking right and would anyone give some direction as to how to Do that?