Dlookup & criteria No 2

[Alain]

Hi Again,

I would like to know what is the correct syntax for the following function
with 2 criteria as dates, with one date as a criteria, it is working just
good but with 2 dates as creiteria, still getting a syntax,
Also, can it be coded using the Between And instead of the following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]=" & num &
"And [from]>=#" & omtdate & "# And [to]<=#" & omtdate & "#""")

Many thanks in advance

Alain

 

[Allen Browne]

That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy") & "#
Between [from] And [to])"

 

[Alain]

Thanks Allen,

I just tried and I'm getting a syntax error

Alain

That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy") & "#
Between [from] And [to])"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like to know what is the correct syntax for the following
function with 2 criteria as dates, with one date as a criteria, it is
working just good but with 2 dates as creiteria, still getting a syntax,
Also, can it be coded using the Between And instead of the following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]=" & num
& "And [from]>=#" & omtdate & "# And [to]<=#" & omtdate & "#""")

Many thanks in advance

Alain

 

[Allen Browne]

Okay, here's how to debug your code.

Create a string for the 3rd argument like this:
Dim strWhere As String
strWhere = "[idbranch] = " & num & ") AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"
Debug.Print strWhere
temp2 = DLookup("[Annual OMT]", "Budget BasicRental", strWhere)

When it fails, open the Immediate window (Ctrl+G) and look at the string
there. Can you see what's wrong with it?

For example, if "idbranch" is a Text type field, you need extra quotes:
strWhere = "[idbranch] = """ & num & """) AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I just tried and I'm getting a syntax error

Alain

That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy") & "#
Between [from] And [to])"

I would like to know what is the correct syntax for the following
function with 2 criteria as dates, with one date as a criteria, it is
working just good but with 2 dates as creiteria, still getting a syntax,
Also, can it be coded using the Between And instead of the following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]=" &
num & "And [from]>=#" & omtdate & "# And [to]<=#" & omtdate & "#""")

Many thanks in advance

Alain

 

[Alain]

Thanks Allen

I will try the your solution and the immediate window ( never work with it
but will learn it ), thanks for this input

Alain

Okay, here's how to debug your code.

Create a string for the 3rd argument like this:
Dim strWhere As String
strWhere = "[idbranch] = " & num & ") AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"
Debug.Print strWhere
temp2 = DLookup("[Annual OMT]", "Budget BasicRental", strWhere)

When it fails, open the Immediate window (Ctrl+G) and look at the string
there. Can you see what's wrong with it?

For example, if "idbranch" is a Text type field, you need extra quotes:
strWhere = "[idbranch] = """ & num & """) AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I just tried and I'm getting a syntax error

Alain

That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy") &
"# Between [from] And [to])"



I would like to know what is the correct syntax for the following
function with 2 criteria as dates, with one date as a criteria, it is
working just good but with 2 dates as creiteria, still getting a
syntax,
Also, can it be coded using the Between And instead of the following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]=" &
num & "And [from]>=#" & omtdate & "# And [to]<=#" & omtdate & "#""")

Many thanks in advance

Alain

 

[Alain]

Hi Allen

For my understanding, I ran with the debug.print ( should have learned that
sooner) and it gives me an error that there is an extra parenteses in the
strWhere, I will make some test to solve the problem myself but me question
is : if me criteria is in a string variable, string requires quote, should
the variable in the criteria part of the DlookUp function requires to be in
quotes also ??
there is really not much help on this in Access for this

Thanks

Alain

Thanks Allen

I will try the your solution and the immediate window ( never work with it
but will learn it ), thanks for this input

Alain

Okay, here's how to debug your code.

Create a string for the 3rd argument like this:
Dim strWhere As String
strWhere = "[idbranch] = " & num & ") AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"
Debug.Print strWhere
temp2 = DLookup("[Annual OMT]", "Budget BasicRental", strWhere)

When it fails, open the Immediate window (Ctrl+G) and look at the string
there. Can you see what's wrong with it?

For example, if "idbranch" is a Text type field, you need extra quotes:
strWhere = "[idbranch] = """ & num & """) AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I just tried and I'm getting a syntax error

Alain


That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy") &
"# Between [from] And [to])"



I would like to know what is the correct syntax for the following
function with 2 criteria as dates, with one date as a criteria, it is
working just good but with 2 dates as creiteria, still getting a
syntax,
Also, can it be coded using the Between And instead of the following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]=" &
num & "And [from]>=#" & omtdate & "# And [to]<=#" & omtdate & "#""")

Many thanks in advance

Alain

 

[Allen Browne]

Yes, the Debug window is very useful.

The string the prints in the Debug window should not have quote marks
showing at the start and end.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

For my understanding, I ran with the debug.print ( should have learned
that sooner) and it gives me an error that there is an extra parenteses in
the strWhere, I will make some test to solve the problem myself but me
question is : if me criteria is in a string variable, string requires
quote, should the variable in the criteria part of the DlookUp function
requires to be in quotes also ??
there is really not much help on this in Access for this

Thanks

Alain

Thanks Allen

I will try the your solution and the immediate window ( never work with
it but will learn it ), thanks for this input

Alain

Okay, here's how to debug your code.

Create a string for the 3rd argument like this:
Dim strWhere As String
strWhere = "[idbranch] = " & num & ") AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"
Debug.Print strWhere
temp2 = DLookup("[Annual OMT]", "Budget BasicRental", strWhere)

When it fails, open the Immediate window (Ctrl+G) and look at the string
there. Can you see what's wrong with it?

For example, if "idbranch" is a Text type field, you need extra quotes:
strWhere = "[idbranch] = """ & num & """) AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"


Thanks Allen,

I just tried and I'm getting a syntax error

Alain


That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy") &
"# Between [from] And [to])"



I would like to know what is the correct syntax for the following
function with 2 criteria as dates, with one date as a criteria, it is
working just good but with 2 dates as creiteria, still getting a
syntax,
Also, can it be coded using the Between And instead of the
following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]=" &
num & "And [from]>=#" & omtdate & "# And [to]<=#" & omtdate & "#""")

Many thanks in advance

Alain

 

[Alain]

Thank you very much for your help, I got more grey hair but learn a few
things
Everything is working just fine

Alain

Yes, the Debug window is very useful.

The string the prints in the Debug window should not have quote marks
showing at the start and end.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

For my understanding, I ran with the debug.print ( should have learned
that sooner) and it gives me an error that there is an extra parenteses
in the strWhere, I will make some test to solve the problem myself but me
question is : if me criteria is in a string variable, string requires
quote, should the variable in the criteria part of the DlookUp function
requires to be in quotes also ??
there is really not much help on this in Access for this

Thanks

Alain

Thanks Allen

I will try the your solution and the immediate window ( never work with
it but will learn it ), thanks for this input

Alain


Okay, here's how to debug your code.

Create a string for the 3rd argument like this:
Dim strWhere As String
strWhere = "[idbranch] = " & num & ") AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"
Debug.Print strWhere
temp2 = DLookup("[Annual OMT]", "Budget BasicRental", strWhere)

When it fails, open the Immediate window (Ctrl+G) and look at the
string there. Can you see what's wrong with it?

For example, if "idbranch" is a Text type field, you need extra quotes:
strWhere = "[idbranch] = """ & num & """) AND (#" & _
Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"


Thanks Allen,

I just tried and I'm getting a syntax error

Alain


That's basically right, though:
- It's lacking a space between the number and the And.
- There's a spurious quote mark at the end.
- The dates need to be in American format.
- It will be faulty if num or omtdate are null.

Try:
"[idbranch] = " & num & ") AND (#" & Format(omtdate, "mm\/dd\/yyyy")
& "# Between [from] And [to])"



I would like to know what is the correct syntax for the following
function with 2 criteria as dates, with one date as a criteria, it
is working just good but with 2 dates as creiteria, still getting a
syntax,
Also, can it be coded using the Between And instead of the
following:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch]="
& num & "And [from]>=#" & omtdate & "# And [to]<=#" & omtdate &
"#""")

Many thanks in advance

Alain