Problem with DLookup

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
 
If txtqtr1 is text it would look like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = ' " & me.txtqtr1 & " ' " )

If txtqtr1 is a date it would look like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = # " & me.txtqtr1 & " # " )

if txtqtr1 is numeric it would look like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = & me.txtqtr1 )

HTH,
Debbie


| Can anyone tell me why I'm not getting an answer to this in a calculated
| control
| =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")
|
| tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
| on a page on a tabcontrol on a form and txtqtr1 is a calculated control
| using
| =DateAdd("m",-3,[txtmonthlabel])
|
| I've tried it with [ ] brackets and without but I don't get any value shown.
|
| The form is based on a query which is based on my table tblmaintabs
| TIA
| Tony
|
|
 
Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
Click to expand...

Your syntax is incorrect.
It depends upon the Datatype of the Field used as criteria.
What is the datatype of [txtMonthLabel]?
If it is a text datatype, you would write the where clause like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
[ControlNameOnform] & "'")

If however, [txtMontLabel] is a Number datatype,then use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
[ControlNameOnform])

If your [txtMonthLabel] is a Date datatype, as it appears to be, then:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
[ControlNameOnform] & "#")

So, using your field names, you want to use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
[txtqtr1] & "#")
 
Thanks to you both for those answers I've used them to tidy up my code
Tony
fredg said:
Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
Click to expand...

Your syntax is incorrect.
It depends upon the Datatype of the Field used as criteria.
What is the datatype of [txtMonthLabel]?
If it is a text datatype, you would write the where clause like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
[ControlNameOnform] & "'")

If however, [txtMontLabel] is a Number datatype,then use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
[ControlNameOnform])

If your [txtMonthLabel] is a Date datatype, as it appears to be, then:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
[ControlNameOnform] & "#")

So, using your field names, you want to use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
[txtqtr1] & "#")
Click to expand...
 
Debbie txtqtr1 is a date type but I now get an error message which says
expression contains invalid date value.
Any ideas
 
Fred tried you code and got syntax error message You may have entered an
operand without an operator?
Any ideas?
Thanks
Tony
fredg said:
Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
Click to expand...

Your syntax is incorrect.
It depends upon the Datatype of the Field used as criteria.
What is the datatype of [txtMonthLabel]?
If it is a text datatype, you would write the where clause like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
[ControlNameOnform] & "'")

If however, [txtMontLabel] is a Number datatype,then use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
[ControlNameOnform])

If your [txtMonthLabel] is a Date datatype, as it appears to be, then:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
[ControlNameOnform] & "#")

So, using your field names, you want to use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
[txtqtr1] & "#")
Click to expand...
 
Tony,

What is in txtMonthlabel? What is it's Data Type and Format?
What is txtqtr1's Format?
What is the name of control you're using this DlookUp in?
What is in txtDomfacsoleqtr?

I'm confused now ... you stated "tblmaintabs is my table and txtDomfacsoleqtr
and txtMonthlabel are controls on a page on a tabcontrol on a form". If
txtDomfacsoleqtr is already on the form for that record, why are you looking it
up?

I found this in DateAdd's Help:

The format of the return value for DateAdd is determined by Control Panel
settings, not by the format that is passed in date argument. If month values
are names, the name must be consistent with the current Calendar property
setting. To minimize the possibility of month names conflicting with the current
Calendar property setting, enter numeric month values (Short Date format).

Debbie

| Debbie txtqtr1 is a date type but I now get an error message which says
| expression contains invalid date value.
| Any ideas
| | > If txtqtr1 is text it would look like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = ' " &
| me.txtqtr1 & " ' " )
| >
| > If txtqtr1 is a date it would look like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = # " &
| me.txtqtr1 & " # " )
| >
| > if txtqtr1 is numeric it would look like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = &
| me.txtqtr1 )
| >
| > HTH,
| > Debbie
| >
| >
| | > | Can anyone tell me why I'm not getting an answer to this in a calculated
| > | control
| > |
| =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")
| > |
| > | tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are
| controls
| > | on a page on a tabcontrol on a form and txtqtr1 is a calculated control
| > | using
| > | =DateAdd("m",-3,[txtmonthlabel])
| > |
| > | I've tried it with [ ] brackets and without but I don't get any value
| shown.
| > |
| > | The form is based on a query which is based on my table tblmaintabs
| > | TIA
| > | Tony
 
His example is missing an & before [txtqtr1].


| Fred tried you code and got syntax error message You may have entered an
| operand without an operator?
| Any ideas?
| Thanks
| Tony
| | > On Mon, 14 Feb 2005 17:26:01 +0000 (UTC), Tony Williams wrote:
| >
| > > Can anyone tell me why I'm not getting an answer to this in a calculated
| > > control
| > >
| =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")
| > >
| > > tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are
| controls
| > > on a page on a tabcontrol on a form and txtqtr1 is a calculated control
| > > using
| > > =DateAdd("m",-3,[txtmonthlabel])
| > >
| > > I've tried it with [ ] brackets and without but I don't get any value
| shown.
| > >
| > > The form is based on a query which is based on my table tblmaintabs
| > > TIA
| > > Tony
| >
| > Your syntax is incorrect.
| > It depends upon the Datatype of the Field used as criteria.
| > What is the datatype of [txtMonthLabel]?
| > If it is a text datatype, you would write the where clause like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
| > [ControlNameOnform] & "'")
| >
| > If however, [txtMontLabel] is a Number datatype,then use:
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
| > [ControlNameOnform])
| >
| > If your [txtMonthLabel] is a Date datatype, as it appears to be, then:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
| > [ControlNameOnform] & "#")
| >
| > So, using your field names, you want to use:
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
| > [txtqtr1] & "#")
| >
| > --
| > Fred
| > Please only reply to this newsgroup.
| > I do not reply to personal email.
 
Back
Top