Code won't work with additions...

[Guest]

My command button developed on my choice form with the wizard is working when
a plan and product choice is given. However, I added code to also narrow the
user's choice for form view an effective date. (This is a database that will
hold contract information & the contracts are identified by plan, product &
effective date.)
The following is my code. The only thing I can think of as to why it's not
working is that "cmbEffectiveDate" control has no query or row source. But I
don't have a table specifically for the effective dates. The effective dates
are only in the table that is linked to form that is opening.
Thanks in advance for your help.

Private Sub cmdClickforContractInformation_Click()
On Error GoTo Err_cmdClickforContractInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"

stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & " AND
[EffectiveDate]=" & "#" & Me![cmbEffectiveDate] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation_Clic:
Exit Sub

Err_cmdClickforContractInformation_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation_Clic

End Sub

 

[Guest]

Build the strLinkCriteria string conditionally. Only include the date if a
date is specified.


stDocName = "frmContractRates-Specifications"

strLinkCriteria = "[ProductId]=" & Me![cmbProduct]
if not isnull(me.cmbEffectiveDate) then
strLinkCriteria = strLinkCriteria & " and [EffectiveDate] = #" &
me.cmbEffectiveDate & "#"
endif

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Albert D.Kallal]

If the date is actualy entered, then you need to "convert", or "force" the
format to usa date format.

stLinkCriteria = "[ProductId] = " & Me![cmbProduct] & _
" AND [EffectiveDate] = " & _
"#" & format(Me![cmbEffectiveDate],"mm/dd/yyyy") & "#"

msgbox stLinkCriteria <---- remove this line when it works!!

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Guest]

I'm getting a syntex error.

Build the strLinkCriteria string conditionally. Only include the date if a
date is specified.


stDocName = "frmContractRates-Specifications"

strLinkCriteria = "[ProductId]=" & Me![cmbProduct]
if not isnull(me.cmbEffectiveDate) then
strLinkCriteria = strLinkCriteria & " and [EffectiveDate] = #" &
me.cmbEffectiveDate & "#"
endif

DoCmd.OpenForm stDocName, , , stLinkCriteria

My command button developed on my choice form with the wizard is working when
a plan and product choice is given. However, I added code to also narrow the
user's choice for form view an effective date. (This is a database that will
hold contract information & the contracts are identified by plan, product &
effective date.)
The following is my code. The only thing I can think of as to why it's not
working is that "cmbEffectiveDate" control has no query or row source. But I
don't have a table specifically for the effective dates. The effective dates
are only in the table that is linked to form that is opening.
Thanks in advance for your help.

Private Sub cmdClickforContractInformation_Click()
On Error GoTo Err_cmdClickforContractInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"

stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & " AND
[EffectiveDate]=" & "#" & Me![cmbEffectiveDate] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation_Clic:
Exit Sub

Err_cmdClickforContractInformation_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation_Clic

End Sub

 

[Guest]

I'm getting a syntex error

 

[Guest]

which line is giving you the error? Double-check the control names, too.

I'm getting a syntex error

If the date is actualy entered, then you need to "convert", or "force" the
format to usa date format.

stLinkCriteria = "[ProductId] = " & Me![cmbProduct] & _
" AND [EffectiveDate] = " & _
"#" & format(Me![cmbEffectiveDate],"mm/dd/yyyy") & "#"

msgbox stLinkCriteria <---- remove this line when it works!!

DoCmd.OpenForm stDocName, , , stLinkCriteria

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Guest]

It's the underscore that gets the error immediately.
I just realized I spelled syntax wrong...sorry.

I'm getting a syntex error.

Build the strLinkCriteria string conditionally. Only include the date if a
date is specified.


stDocName = "frmContractRates-Specifications"

strLinkCriteria = "[ProductId]=" & Me![cmbProduct]
if not isnull(me.cmbEffectiveDate) then
strLinkCriteria = strLinkCriteria & " and [EffectiveDate] = #" &
me.cmbEffectiveDate & "#"
endif

DoCmd.OpenForm stDocName, , , stLinkCriteria

My command button developed on my choice form with the wizard is working when
a plan and product choice is given. However, I added code to also narrow the
user's choice for form view an effective date. (This is a database that will
hold contract information & the contracts are identified by plan, product &
effective date.)
The following is my code. The only thing I can think of as to why it's not
working is that "cmbEffectiveDate" control has no query or row source. But I
don't have a table specifically for the effective dates. The effective dates
are only in the table that is linked to form that is opening.
Thanks in advance for your help.

Private Sub cmdClickforContractInformation_Click()
On Error GoTo Err_cmdClickforContractInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"

stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & " AND
[EffectiveDate]=" & "#" & Me![cmbEffectiveDate] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation_Clic:
Exit Sub

Err_cmdClickforContractInformation_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation_Clic

End Sub

 

[Albert D.Kallal]

I'm getting a syntex error

What line do you get a syntax error on?

We need at least some rudimentary code skills here, as trying to write code
with no idea as to how this works is really a shot in the dark.
(I not sure if I am actually helping you..or making your situation worse by
offering you something you can't handle).

Anyway...lets try a few things

stLinkCriteria = "[ProductId] = " & Me![cmbProduct] & _
" AND [EffectiveDate] = " & _
"#" & format(Me![cmbEffectiveDate],"mm/dd/yyyy") & "#"

after you enter the above code, then do a debug->compile. Does the code
compile? (you need to do this BEFORE you try and run the code).

Note also how the above is actually on 3 lines. You need to type it in as
above. So, word wrapping is important here.

 

[Guest]

Ok. It does compile.
Here is the code I wrote like you said:

stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & _
"AND [EffectiveDate]=" & _
"#" & Format(Me![cmbEffectiveDate], "mm/dd/yyyy") & "#"

Now when I go to the form I choose the Plan and the Product. However, when
I go to choose the Effective Date, (it's blank), & I hit the command button I
get the following error:

Syntax error in date in query expression '[ProductId]=2AND[EffectiveDate]=##'

Can you help me fix that error? You have been so patient. It's really
appreciated.

I'm getting a syntex error

What line do you get a syntax error on?

We need at least some rudimentary code skills here, as trying to write code
with no idea as to how this works is really a shot in the dark.
(I not sure if I am actually helping you..or making your situation worse by
offering you something you can't handle).

Anyway...lets try a few things

stLinkCriteria = "[ProductId] = " & Me![cmbProduct] & _
" AND [EffectiveDate] = " & _
"#" & format(Me![cmbEffectiveDate],"mm/dd/yyyy") & "#"

after you enter the above code, then do a debug->compile. Does the code
compile? (you need to do this BEFORE you try and run the code).

Note also how the above is actually on 3 lines. You need to type it in as
above. So, word wrapping is important here.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Syntax error in date in query expression
'[ProductId]=2AND[EffectiveDate]=##'

Ah..ok...the above seems to be missing spaces. Remember, it does need to
"read" like normal sql.

so, it should read

Syntax error in date in query expression '[ProductId] = 2 AND
[EffectiveDate]=##'

Note the "2AND" is changed to 2 AND

Here is the code I wrote like you said:

Try adding the extra space before the "and".

stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & _
" AND [EffectiveDate] =" & _ <- NOTE THE SPACE FOR "AND"
"#" & Format(Me![cmbEffectiveDate], "mm/dd/yyyy") & "#"

msgbox stLinkCriteria <--- this can be removed when we get it
working

msgbox me!cmbEffectiveDate <---- does hits show a valid date?

I think our effective date might be the problem. Those above msgbox commands
are just there to "show" you what things look like...

 

[Guest]

Thanks for all your help. I think you are right it's the effective date & it
just doesn't want to work. So, I've deleted it from my choices for the users
& they will have to just scroll for the date they want.
I'm being pressured by the users to get this done yesterday.
Again, thanks for all your time, patience, & help.
Happy Holidays!

Syntax error in date in query expression
'[ProductId]=2AND[EffectiveDate]=##'

Ah..ok...the above seems to be missing spaces. Remember, it does need to
"read" like normal sql.

so, it should read

Syntax error in date in query expression '[ProductId] = 2 AND
[EffectiveDate]=##'

Note the "2AND" is changed to 2 AND

Here is the code I wrote like you said:

Try adding the extra space before the "and".

stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & _
" AND [EffectiveDate] =" & _ <- NOTE THE SPACE FOR "AND"
"#" & Format(Me![cmbEffectiveDate], "mm/dd/yyyy") & "#"

msgbox stLinkCriteria <--- this can be removed when we get it
working

msgbox me!cmbEffectiveDate <---- does hits show a valid date?

I think our effective date might be the problem. Those above msgbox commands
are just there to "show" you what things look like...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal