If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using wildcards in dlookup
I have a colum containing the following:
row tag date 1 111- 1/5/05 2 222- 2/6/05 3 222-111 2/8/05 4 222-111-333 11/5/04 5 222-111-333-444 9/5/04 I'm trying to use a dlookup formula in an expression in another column in the query that will give me the earliest date of either the same row or a row that contains a tag that has the same beginning characters. For example, row 5 would look at row 5, 4, 3, & 2 and row 3 would look at row 2. I can successfully create a dlookup formula to to retrieve the earlies date from the entire table Date column, but I'm having trouble with the criteria portion of the dlookup syntax. Any ideas? Thank you much |
#2
|
|||
|
|||
Read this first: http://www.mvps.org/access/queries/qry0020.htm
-- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Alex" wrote in message ... I have a colum containing the following: row tag date 1 111- 1/5/05 2 222- 2/6/05 3 222-111 2/8/05 4 222-111-333 11/5/04 5 222-111-333-444 9/5/04 I'm trying to use a dlookup formula in an expression in another column in the query that will give me the earliest date of either the same row or a row that contains a tag that has the same beginning characters. For example, row 5 would look at row 5, 4, 3, & 2 and row 3 would look at row 2. I can successfully create a dlookup formula to to retrieve the earlies date from the entire table Date column, but I'm having trouble with the criteria portion of the dlookup syntax. Any ideas? Thank you much |
#3
|
|||
|
|||
This will help a lot; thanks so much.
I'm still confused though, on how to define my group. In the example you linked me to, records are grouped by BookID, which are all the same. I need to group by Tags that contain the same beginning characters. For example: Row 2 would be in a group with row 3, 4, & 5. Row 3 would be in a group with rows 4 & 5. Row 6 would be in a group by itself. (my examples in my first email below are incorrect; sorry, it was getting later in the day) I've tried to use Like in the criteria row in the query grid in Tag field, which is grouped, but keep getting error messages. I really appreciate your help on this; I'm just stumped. row tag date 1 111- 1/5/05 2 222- 2/6/05 3 222-111 2/8/05 4 222-111-333 11/5/04 5 222-111-333-444 9/5/04 6 333-111-333-444-555 10/2/04 "[MVP] S.Clark" wrote: Read this first: http://www.mvps.org/access/queries/qry0020.htm -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Alex" wrote in message ... I have a colum containing the following: row tag date 1 111- 1/5/05 2 222- 2/6/05 3 222-111 2/8/05 4 222-111-333 11/5/04 5 222-111-333-444 9/5/04 I'm trying to use a dlookup formula in an expression in another column in the query that will give me the earliest date of either the same row or a row that contains a tag that has the same beginning characters. For example, row 5 would look at row 5, 4, 3, & 2 and row 3 would look at row 2. I can successfully create a dlookup formula to to retrieve the earlies date from the entire table Date column, but I'm having trouble with the criteria portion of the dlookup syntax. Any ideas? Thank you much |
#4
|
|||
|
|||
Maybe this will help:
My SQL is the following. I'm just not getting the Like part correct. SELECT DLookUp("Min([Date])","[Query1]","Like [Tag] & "*"") AS NewDate FROM Query1; "[MVP] S.Clark" wrote: Read this first: http://www.mvps.org/access/queries/qry0020.htm -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Alex" wrote in message ... I have a colum containing the following: row tag date 1 111- 1/5/05 2 222- 2/6/05 3 222-111 2/8/05 4 222-111-333 11/5/04 5 222-111-333-444 9/5/04 I'm trying to use a dlookup formula in an expression in another column in the query that will give me the earliest date of either the same row or a row that contains a tag that has the same beginning characters. For example, row 5 would look at row 5, 4, 3, & 2 and row 3 would look at row 2. I can successfully create a dlookup formula to to retrieve the earlies date from the entire table Date column, but I'm having trouble with the criteria portion of the dlookup syntax. Any ideas? Thank you much |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
DLookUp for multiple forms | [email protected] | Using Forms | 4 | January 9th, 2005 10:48 AM |
dlookup missing criteria? | Dave | Running & Setting Up Queries | 2 | January 3rd, 2005 10:17 PM |
Dlookup | Peter | Running & Setting Up Queries | 2 | August 5th, 2004 08:39 AM |
DLOOKUP in an external Database gives TYPE Mismatch-Error | Reiner Harmgardt | General Discussion | 1 | July 22nd, 2004 09:00 AM |
DLookup Function in Queries | Jim | Running & Setting Up Queries | 1 | June 10th, 2004 07:07 PM |