Trouble with Lookup - need something similar to VLookup

  • Thread starter Thread starter Expert Gnu-B
  • Start date Start date
E

Expert Gnu-B

Background:
(I've been using Access for a few years and know enough advanced techniques
to get myself into trouble, but not out of it!) I have a table (tblSurveys)
that tracks survey results for several projects (tblProjects). I need to
create a report that shows various statistics (Count, Average, etc.) based on:
1) the current week (I can get these just fine),
2) the fiscal month-to-date, and
3) the fiscal year-to-date.
The problem is that our fiscal dates are not pretty - they start and end on
various days throughout the month (Ex: October ended 10/24, November ends
11/21, etc.). I have created a separate table (tblFM) to track the start/end
dates for each month.

What I'm looking for:
I'm trying to create an expression - either in the underlying query or the
report itself - that will in essence "look at the date of each Survey
([Date_Submit]), and return the fiscal month ([FMID]) it was submitted in, as
a new variable (say, [FMSubmit])". That way I can then count (average, etc.)
those surveys submitted within the current fiscal month.

When I try to use DLookup in the query like so:

FMSubmit: DLookUp("FMID","tblFM","[Date_Submit] BETWEEN tblFM.FM_Start AND
tblFM.FM_End")

it tells me "The expression you entered as a query parameter produced this
error: 'Microsoft Office Access can't find the name 'Date_Submit' you entered
in the expression".

I have scoured this site for answers and haven't found one that gets me the
results I need. I ran across Allen Browne's tutorials on subqueries, but I
don't understand how/if they would apply to my situation.

Any help would be greatly appreciated! If you need more information, please
ask. (I am currently using Access 2007).
 
It might work if you concatenate the Date_Submit into the 3rd string for
DLookup(). Example:

DLookUp("FMID","tblFM", Format([Date_Submit], "\#mm\/dd\/yyyy\#") & "
BETWEEN FM_Start AND FM_End")

Obviously that 3rd string won't be right if Date_Submit is null, and JET may
not recognise it as a date, so you probably want:

CVDate( IIf( [Date_Submit] Is Null, Null, DLookUp("FMID","tblFM",
Format([Date_Submit], "\#mm\/dd\/yyyy\#") & " BETWEEN FM_Start AND
FM_End")))

(Note: Do not change the formatting to match your own region's date
settings.)
 
Your first solution worked perfect! Thank you!
(Out of curiosity, why does yours work when mine did not?)

Allen Browne said:
It might work if you concatenate the Date_Submit into the 3rd string for
DLookup(). Example:

DLookUp("FMID","tblFM", Format([Date_Submit], "\#mm\/dd\/yyyy\#") & "
BETWEEN FM_Start AND FM_End")

Obviously that 3rd string won't be right if Date_Submit is null, and JET may
not recognise it as a date, so you probably want:

CVDate( IIf( [Date_Submit] Is Null, Null, DLookUp("FMID","tblFM",
Format([Date_Submit], "\#mm\/dd\/yyyy\#") & " BETWEEN FM_Start AND
FM_End")))

(Note: Do not change the formatting to match your own region's date
settings.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Expert Gnu-B said:
Background:
(I've been using Access for a few years and know enough advanced
techniques
to get myself into trouble, but not out of it!) I have a table
(tblSurveys)
that tracks survey results for several projects (tblProjects). I need to
create a report that shows various statistics (Count, Average, etc.) based
on:
1) the current week (I can get these just fine),
2) the fiscal month-to-date, and
3) the fiscal year-to-date.
The problem is that our fiscal dates are not pretty - they start and end
on
various days throughout the month (Ex: October ended 10/24, November ends
11/21, etc.). I have created a separate table (tblFM) to track the
start/end
dates for each month.

What I'm looking for:
I'm trying to create an expression - either in the underlying query or the
report itself - that will in essence "look at the date of each Survey
([Date_Submit]), and return the fiscal month ([FMID]) it was submitted in,
as
a new variable (say, [FMSubmit])". That way I can then count (average,
etc.)
those surveys submitted within the current fiscal month.

When I try to use DLookup in the query like so:

FMSubmit: DLookUp("FMID","tblFM","[Date_Submit] BETWEEN tblFM.FM_Start AND
tblFM.FM_End")

it tells me "The expression you entered as a query parameter produced this
error: 'Microsoft Office Access can't find the name 'Date_Submit' you
entered
in the expression".

I have scoured this site for answers and haven't found one that gets me
the
results I need. I ran across Allen Browne's tutorials on subqueries, but
I
don't understand how/if they would apply to my situation.

Any help would be greatly appreciated! If you need more information,
please
ask. (I am currently using Access 2007).
Click to expand...
Click to expand...
 
Several reasons, but mostly because you concatenated the value into the 3rd
argument.

You might be able to get yours working with a fully-qualified reference in
the string, such as:
[Forms].[Form1].[Date_Submit]
But I've had much better experiences with concatenating into the string.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Expert Gnu-B said:
Your first solution worked perfect! Thank you!
(Out of curiosity, why does yours work when mine did not?)

Allen Browne said:
It might work if you concatenate the Date_Submit into the 3rd string for
DLookup(). Example:

DLookUp("FMID","tblFM", Format([Date_Submit], "\#mm\/dd\/yyyy\#") & "
BETWEEN FM_Start AND FM_End")

Obviously that 3rd string won't be right if Date_Submit is null, and JET
may
not recognise it as a date, so you probably want:

CVDate( IIf( [Date_Submit] Is Null, Null, DLookUp("FMID","tblFM",
Format([Date_Submit], "\#mm\/dd\/yyyy\#") & " BETWEEN FM_Start AND
FM_End")))

(Note: Do not change the formatting to match your own region's date
settings.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Expert Gnu-B said:
Background:
(I've been using Access for a few years and know enough advanced
techniques
to get myself into trouble, but not out of it!) I have a table
(tblSurveys)
that tracks survey results for several projects (tblProjects). I need
to
create a report that shows various statistics (Count, Average, etc.)
based
on:
1) the current week (I can get these just fine),
2) the fiscal month-to-date, and
3) the fiscal year-to-date.
The problem is that our fiscal dates are not pretty - they start and
end
on
various days throughout the month (Ex: October ended 10/24, November
ends
11/21, etc.). I have created a separate table (tblFM) to track the
start/end
dates for each month.

What I'm looking for:
I'm trying to create an expression - either in the underlying query or
the
report itself - that will in essence "look at the date of each Survey
([Date_Submit]), and return the fiscal month ([FMID]) it was submitted
in,
as
a new variable (say, [FMSubmit])". That way I can then count (average,
etc.)
those surveys submitted within the current fiscal month.

When I try to use DLookup in the query like so:

FMSubmit: DLookUp("FMID","tblFM","[Date_Submit] BETWEEN tblFM.FM_Start
AND
tblFM.FM_End")

it tells me "The expression you entered as a query parameter produced
this
error: 'Microsoft Office Access can't find the name 'Date_Submit' you
entered
in the expression".

I have scoured this site for answers and haven't found one that gets me
the
results I need. I ran across Allen Browne's tutorials on subqueries,
but
I
don't understand how/if they would apply to my situation.

Any help would be greatly appreciated! If you need more information,
please
ask. (I am currently using Access 2007).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top