Dlooup Problem

[mohammadagul]

hello friends
i have a small DlooupProblem here

tblLed
InvoiceDate - Date
Amount - Number

tblCal
D1
DAmount
D2
D2Amount
D3
D3Amount
....
D42

i have a form with a control source to tblCal
i am trying to use the DlookUp function as follows

Tried this on After update, On Current, On Exit, , but no use..

Dim X as Variant

X = DLookup("Amount" , "tblLed" , "InvoiceDAte = #" & forms!frmCal!D1 & "#")

Me.D1Amount = X

it does not give any error and does not give the value also

tried it in immediate window also and it shows a null value..
can anyone tell mewhat iam doing wrong here and why this code is not lookling ups the value.

 

[Mile-O]

D1, D2, D3, ...D42 - that's not normalised data you are working with.

 

[namliam]

You are probably having trouble with the date syntax.

put a line in just before the x=
debug.print format(forms!frmCal!D1 ,"dd-mmm-yyyy")

and check the immidiat window I think you will find that the date is not the one you want.

Also check your normalisation as SJ said, this is "bad practice" and will bite you in the ... in the future....

Regards

 

[mohammadagul]

yes, i do agree with yo that this is not a normilized data. but i have to use it. Actually ia m trying tomake a sales calender. the calender contro has 42 textboxes. what i want is to check in my table the what amount of sales is available in connection with the textbox Data. if D1 is 01/03/2005 and the same date is present in the tblLed Table i want it to take the amount and show it in my unboun text box.
i tried you suggestion namliam, but still no good.
Abny other suggestion inthis respect would be much help.

thanks

muhammad Atif Gul

 

[namliam]

Why an unbound text box at all?

And what did it say in the immidiat window? The proper date?

Greetz

 

[RossWindows]

One question. Will you ever need to input data into tblCal?

 

[Poppa Smurf]

Try
X = DLookup("Amount" , "tblLed" , "InvoiceDAte = #" & Format([forms]![frmCal]![D1], "mm/dd/yyyy") & "#")

When using DLookup the date MUST BE in the format of mm dd yyyy

 

[Galaxiom]

Even though you may be receiving data like that in the clearly denormalised tblCal (presumably from a spreadsheet) your first step on import should be to normalise it. Otherwise you are going to be continually performing clumsy workarounds.

 

[RossWindows]

Even though you may be receiving data like that in the clearly denormalised tblCal (presumably from a spreadsheet) your first step on import should be to normalise it. Otherwise you are going to be continually performing clumsy workarounds.

Agreed.
If you receive the tblCal data on a regular basis, you can use a UNION query to transpose columns to rows. This might be your best bet.
However, If you have to then edit or add-to the data in tblCal it becomes more complicated when you are using a UNION query.

The UNION query would basically take this data:

ID D1 D2 D3 D4 D5
1 1 5 10 20 99
2 2 4 11 30 98
3 3 3 12 40 87
...

And transpose it into this:

ID Field Value
1 D1 1
1 D2 5
1 D3 10
1 D4 20
1 D5 99
2 D1 2
2 D2 4
2 D3 11
2 D4 30
2 D5 98
3 D1 3
3 D2 3
3 D3 12
3 D4 40
3 D5 87
...


(It is much easier to work with data in this format)
I would then take this data and append it to a normalized table, maybe called tblCalNorm, then delete the data in tblCal.
Repeat every time you get new data.

The query would look something like this:

SELECT ID As ID, "D1" As Field, D1 As Value FROM tblCal
UNION SELECT ID As ID, "D2" As Field, D2 As Value FROM tblCal
UNION SELECT ID As ID, "D3" As Field, D3 As Value FROM tblCal
UNION SELECT ID As ID, "D4" As Field, D4 As Value FROM tblCal
UNION SELECT ID As ID, "D5" As Field, D5 As Value FROM tblCal
...;