Dlookup

[Guest]

I would be pleased if someone could help med to crack this one.

I have two tables. One holds information on invoice transactions including the field issue date (DD-MM-YY hh:mm:ss). The other table holds information on which week number a certain date is related to, (August 4 2004 has weeknumber 32). The data in the latter table is stored as numbers meaning that August 4 is stored as 38203. In the result of my Query to the invoice table I would like to include the weekno. My best idea for doing this is to combine the Int function and the Dlookup in a calculated field in the query.

I have done this in the following way, it however does not work and I get a syntax error.

Weekno: DLookUp("[Weekno]";"Weektable";"[Datenumber] =" & Int([Invoice]![Issuedate]))

I have checked the Int function as stand alone and it generates the wanted result.

If I make Dlookup equal to for instance:

Weekno: DLookUp("[Weekno]";"Weektable";"[Datenumber] = 38203") then it correctly returns the value.

But in combination.....

Thank you for any help

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why don't you use the DatePart() function?

? DatePart("ww", CDate(38203))
32

In a query:

SELECT DatePart("ww", CDate(IssueDate)) As WeekNo ...

If the data type of the IssueDate column is Date, you may wish to try
this:

SELECT DatePart("ww", IssueDate) As WeekNo ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQREecoechKqOuFEgEQJv0gCeOJOwcyd7oDEJjDmISekaaMZPvEEAn05q
wao5UN6T+3x/NdGwTPt4imrD
=3ahl
-----END PGP SIGNATURE-----

 

[Guest]

Thank you very much.

That solved the problem in the most elegant way. I was - as you can see - not aware of this function.

Best regards
Peter


"MGFoster" skrev:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why don't you use the DatePart() function?

? DatePart("ww", CDate(38203))
32

In a query:

SELECT DatePart("ww", CDate(IssueDate)) As WeekNo ...

If the data type of the IssueDate column is Date, you may wish to try
this:

SELECT DatePart("ww", IssueDate) As WeekNo ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQREecoechKqOuFEgEQJv0gCeOJOwcyd7oDEJjDmISekaaMZPvEEAn05q
wao5UN6T+3x/NdGwTPt4imrD
=3ahl
-----END PGP SIGNATURE-----

I would be pleased if someone could help med to crack this one.

I have two tables. One holds information on invoice transactions including the field issue date (DD-MM-YY hh:mm:ss). The other table holds information on which week number a certain date is related to, (August 4 2004 has weeknumber 32). The data in the latter table is stored as numbers meaning that August 4 is stored as 38203. In the result of my Query to the invoice table I would like to include the weekno. My best idea for doing this is to combine the Int function and the Dlookup in a calculated field in the query.

I have done this in the following way, it however does not work and I get a syntax error.

Weekno: DLookUp("[Weekno]";"Weektable";"[Datenumber] =" & Int([Invoice]![Issuedate]))

I have checked the Int function as stand alone and it generates the wanted result.

If I make Dlookup equal to for instance:

Weekno: DLookUp("[Weekno]";"Weektable";"[Datenumber] = 38203") then it correctly returns the value.

But in combination.....

Thank you for any help