DLookup function help ,Error : #NAME

[fluffyvampirekitten]

I have a continuous form and I would like to add in a textbox in the form to display the date of the loaded data



I want to display "DATA FOR PERIOD 2015-11"
Since, I can't use subform in my continuous form. I'm trying to use a D look up function because I'm using another record source for my Form.
However , I keep getting error #NAME.

Anyone could help me ?
Thanks in advance

=DLookUp("[LOAD_PERIOD]","[Load_Date]","[LOAD_PERIOD]"="DATA FOR PERIOD " & Year([LOAD_PERIOD]) & "-" & Format(Month([Load_Period])))

[June7]

Why are you using DLookup to look up load period when you already have the load period value?

You are using [Load_Date] in the argument for domain (table or query). You have a table named Load_Date?


Maybe you just need:

="DATA FOR PERIOD " & Format([Load_Period], "yyyy-mm")

[John_G]

Your DLookup doesn't look right. Do you have a table or query called "Load_Date"? If not, that is a possible source of the error - the DLookup is trying to look up data from a field called "Load_Period" in a table or query called "Load_Date". The where clause in the DLookup doesn't make sense either - it should specify which record the DLookup should retrieve data from, but I can't see how it will.

Is there a form field (control) which contains the data you want to use in the DLookup WHERE clause?

[fluffyvampirekitten]

Your DLookup doesn't look right. Do you have a table or query called "Load_Date"? If not, that is a possible source of the error - the DLookup is trying to look up data from a field called "Load_Period" in a table or query called "Load_Date". The where clause in the DLookup doesn't make sense either - it should specify which record the DLookup should retrieve data from, but I can't see how it will.

Is there a form field (control) which contains the data you want to use in the DLookup WHERE clause?

I do have a query called "Load_Date"

My initial D look up is =DLookUp ( "[Load_Period]", "[Load_Date]" , )
However I get this value " 01/11/15"

So I'm trying to do is to display this "DATA FOR PERIOD 2015-11"
that's why I tried to put this as my criteria (]","[LOAD_PERIOD]"="DATA FOR PERIOD " & Year([LOAD_PERIOD]) & "-" & Format(Month([Load_Period])))

[June7]

The third argument is for filter criteria, like the WHERE clause in SQL statement. Where you are concatenating a string needs to be a field from the source table/query and a parameter to search for. What you need is like:

="DATA FOR PERIOD " & Format(DLookup("[Load_Period]", "[Load_Date]", "[some fieldname in the query] = some value here"), "yyyy-mm")

The filter criteria is optional. Without it, the DLookup will return value from unspecified (maybe first, maybe not) record in the dataset.

[fluffyvampirekitten]

The third argument is for filter criteria, like the WHERE clause in SQL statement. Where you are concatenating a string needs to be a field from the source table/query and a parameter to search for. What you need is like:

="DATA FOR PERIOD " & Format(DLookup("[Load_Period]", "[Load_Date]", "[some fieldname in the query] = some value here"), "yyyy-mm")

The filter criteria is optional. Without it, the DLookup will return value from unspecified (maybe first, maybe not) record in the dataset.

Ahh . I got it Thank you so much
By the way , is there a way to make my text box(load date) value permanent ?
My form have a few combo boxes and when the combo box value is changed , the whole page will be refreshed. Thus , the text box(load date) will be refreshed as well ,

Is there any way to make it like permanent ?
I don't want my text box to be refreshed unless when I re-open the form ?

[June7]

Sorry, don't really understand the question. If textbox is bound to a field or has dynamic expression, it will change as the data changes.

[fluffyvampirekitten]

How to make the textbox(load_date) not dynamic ?
cos if I change my combo box value and it does not retrieve/display any data .

The textbox(load_date) will be not displayed

[June7]

Sorry, none of that makes sense to me. I don't know your data structure enough nor what you are trying to accomplish to advise.

[fluffyvampirekitten]

I want to unbound my textbox instead ..

I'm trying to do this instead but still getting error #NAME

Private Sub getLoadDate()
Me.RecordSource = "SELECT * FROM Setting"
Me!txtload_period.ControlSource = "LOAD_PERIOD"
End Sub

Private Sub Form_Load()
    DoCmd.Maximize

    
    getLoadDate
    
End Sub

[June7]

Why are you using VBA to set form and textbox properties? Why don't you just set these properties in form design view?

[fluffyvampirekitten]

In this form , I have unbound combo boxes .
So when I select a value from the combo box and if It does not retrieve anything ,
The load date textbox will not be displayed .

This is the reason why I want to unbound my textbox.

[June7]

Don't really understand that reasoning.

But, try . instead !.

Me.txtload_period.ControlSource = "LOAD_PERIOD"

[fluffyvampirekitten]

When I click on Category "POTENTIAL DUP"
It will display the "LOAD DATE"

When I click on Category "REPEATED"
It does not display the " LOAD DATE"



I tried to do the bound and unbound text box, the result are still the same . Why is that so ?
I don't know what to do already

[June7]

Why are you using code to set ControlSource property of txtload_period with a field name? If that field is not in the form RecordSource, will then get #NAME error in textbox.