Function DLookup

[rdw456]

Hi jdraw, Beetle and sneuberg

That part of the problem is solved with the txtbox set to currency I was not just getting it now it makes sense thanks all. Now I need to input the date from another txtbox when I open the form

Thanks

Bob

 

[sneuberg]

If your are going to change the date from a literal to a textbox you have two ways of putting that into the DLookup. Let's say you name the textbox txtWkEnding. You can concatenate it in like:

DLookUp("PayIns","tbDepositInformation"," WkEnding = #"  & Me.txtWkEnding & "#" )

or you can used the full reference like:

DLookUp("PayIns","tbDepositInformation","WkEnding =  Forms![COLOR="Blue"]NameOfForm[/COLOR]!txtWkEnding" )

where you don't have to concatenate or use delimiters. You would need to change NameOfForm to the name of your form. Sometimes this even works with just the textbox like:

DLookUp("PayIns","tbDepositInformation","WkEnding =  txtWkEnding" )

but I'm not sure when. I just know is won't work this way if the field and control name are the same. In that case you definitely need the full reference for the control (textbox).

 

[rdw456]

hi all
that part of the problem is solved thanks for all the help. My next challange is to have a text box that the user can supply the week ending date part of the DLookup function any idea's welcome.

thanks bob

 

[Beetle]

Did you try any of Steve's suggestions from post #22?

 

[sneuberg]

Rather than a textbox I suggest trying combo box populated with the dates from the tbDepositInformation table. Just make the row source of the combo box

SELECT WkEnding FROM tbDepositInformation

which you can do though the query designer when you click on the ellipsis on the Row Source property of the combo box. Then just add the combo box to the DLookup the same way I described in post 22.

 

[rdw456]

Hi all
here it is working and yes I did go back to previous posts for the solution. Just when you think you have it beat there always seem to have a twist when I open the form I get errors on the two text boxes that have the DLookup function in them and what I want is to display 0 until you enter the date. I am only using this form to work out the solution on the real form I have a calendar. So still working on hopefully the last piece. I must say its been a great leaning for me with all you guys help.

Bob

 

[Beetle]

If you want to be able to leave the WkEnding text box blank upon opening the form, then you are going to have to wrap your DLookup in an IIf statement. It would look like;

=IIf(Nz([Forms]![Form1]![txtWeekEnding],"")<>"",Nz(DLookUp("PayIns","tblDepositInformation"," WkEnding = #" & [Forms]![Form1]![txtWeekEnding] & "#"),0),0)

 

[sneuberg]

Beetle's expression has a space in the "tblDepositInformatio n". Without the space like:

=IIf(Nz([Forms]![Form1]![txtWeekEnding],"")<>"",Nz(DLookUp("PayIns","tblDepositInformation"," WkEnding = #" & [Forms]![Form1]![txtWeekEnding] & "#"),0),0)

seems to work ok.

 

[sneuberg]

A slightly short version of Beatle's expression

=IIf(Isnull([Forms]![Form1]![txtWeekEnding]),0,Nz(DLookUp("PayIns","tblDepositInformation","WkEnding = #" & [Forms]![Form1]![txtWeekEnding] & "#"),0))

Concerning my post #22 I'm glad you figured out that you needed to concatenate in the textbox reference into the criteria of the DLookup in spite my saying you wouldn't have to. I'm baffled by the fact that it doesn't work consistently in a calculated control without being concatenated. It works ok in a subroutine or in the Immediate window. It's a shame it doesn't work because concatenating in date can cause problems for system where the regional settings are set to a dd/mm/yyyy format. In those cases when you concatenate a date it needs to be formatted to the US format mm/dd/yyyy. If you are interested there's more info on this in International Dates in Access by Allen Browne

 

[sneuberg]

This is weird. I just went back to test it one more time and now this

=IIf(IsNull([Forms]![Form1]![txtWeekEnding]),0,Nz(DLookUp("PayIns","tblDepositInformation","WkEnding = [Forms]![Form1]![txtWeekEnding]"),0))

which doesn't have the concatenation starting working.

 

[rdw456]

Hi all

Well I finally have got it all worked out with you guys ( Beetle and sneuberg ) I am still a novice but working on improving my skills

I have uploaded a working model for other people to see so your efforts will help some one else.

 

[rdw456]

Tried to put thanks in with a few details but could not enter anything other than name also want to mark thread as solved

Thanks again Bob

 

[sneuberg]

You can mark a thread as solved in the Thread Tools menu. It's in the second menu bar from the top of the page.