Help with Syntax Too Few Parameters

W

wrightlife11

I am trying to get the earliest date of a set of records. I can't use last
or first because the records came into the database with no correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of the form
control it works fine, but I need to limit the parameters to the form control.
The form is open and the control has data like the value above. I tried to
use the results of the combo box since it is filtering records in the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE (((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
 
J

John Spencer

Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
W

wrightlife11

Thanks....I will try it first thing tomorrow. Also What does the & "' " do
at the end?

John Spencer said:
Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to get the earliest date of a set of records. I can't use last
or first because the records came into the database with no correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of the form
control it works fine, but I need to limit the parameters to the form control.
The form is open and the control has data like the value above. I tried to
use the results of the combo box since it is filtering records in the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE (((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
 
T

Tom van Stiphout

On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP

Thanks....I will try it first thing tomorrow. Also What does the & "' " do
at the end?

John Spencer said:
Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to get the earliest date of a set of records. I can't use last
or first because the records came into the database with no correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of the form
control it works fine, but I need to limit the parameters to the form control.
The form is open and the control has data like the value above. I tried to
use the results of the combo box since it is filtering records in the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE (((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 
B

BeWyched

Hi

You can do this with just one line:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc='" &
Forms!frmFabDocCheckOff!cmboFabDoc) & "'"

This assumes that cmboFabDoc returns a string, otherwise leave out the
single 's. Note that,exagerated, '" is ' " and "'" is " ' ".

Cheers.
BW


Tom van Stiphout said:
On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP

Thanks....I will try it first thing tomorrow. Also What does the & "' " do
at the end?

John Spencer said:
Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


wrightlife11 wrote:
I am trying to get the earliest date of a set of records. I can't use last
or first because the records came into the database with no correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of the form
control it works fine, but I need to limit the parameters to the form control.
The form is open and the control has data like the value above. I tried to
use the results of the combo box since it is filtering records in the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE (((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 
B

BeWyched

Sorry, a typo, the line should read:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc]='" &
Forms!frmFabDocCheckOff!cmboFabDoc & "'")

BeWyched said:
Hi

You can do this with just one line:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc='" &
Forms!frmFabDocCheckOff!cmboFabDoc) & "'"

This assumes that cmboFabDoc returns a string, otherwise leave out the
single 's. Note that,exagerated, '" is ' " and "'" is " ' ".

Cheers.
BW


Tom van Stiphout said:
On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP

Thanks....I will try it first thing tomorrow. Also What does the & "' " do
at the end?

:

Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


wrightlife11 wrote:
I am trying to get the earliest date of a set of records. I can't use last
or first because the records came into the database with no correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of the form
control it works fine, but I need to limit the parameters to the form control.
The form is open and the control has data like the value above. I tried to
use the results of the combo box since it is filtering records in the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE (((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Nice catch, but if you just use DMin, you can keep the
FORMS!formName!controlName inside the string, which removes any kind of
problem with delimiter (and eventual presence of the delimiter itself inside
the value, such as if the value inside cmboFacDoc is 5'10 " 1/8 :


EarlyFlameOnShapes = DMin("SFStartDate",
"tblMain","FabDoc=Forms!frmFabDocCheckOff!cmboFabDoc")


The syntax FORMS!formName!controlName is nice as long as you use the user
interface, DoCmd, properties RowSource or RecordSource, and Dxxx functions.
The syntax is NOT automatically recognized with CurrentDb, but as long as it
IS recognized, keeping it inside the criteria string avoid the delimiter
problems.



Vanderghast, Access MVP


BeWyched said:
Sorry, a typo, the line should read:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc]='" &
Forms!frmFabDocCheckOff!cmboFabDoc & "'")

BeWyched said:
Hi

You can do this with just one line:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc='" &
Forms!frmFabDocCheckOff!cmboFabDoc) & "'"

This assumes that cmboFabDoc returns a string, otherwise leave out the
single 's. Note that,exagerated, '" is ' " and "'" is " ' ".

Cheers.
BW


Tom van Stiphout said:
On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP


Thanks....I will try it first thing tomorrow. Also What does the &
"' " do
at the end?

:

Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate "
& _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


wrightlife11 wrote:
I am trying to get the earliest date of a set of records. I
can't use last
or first because the records came into the database with no
correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too
Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of
the form
control it works fine, but I need to limit the parameters to the
form control.
The form is open and the control has data like the value above.
I tried to
use the results of the combo box since it is filtering records in
the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate
" & _
"FROM tblMain " & _
"WHERE
(((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 
W

wrightlife11

Thanks!!!!! Many times over.

Matthew
BeWyched said:
Sorry, a typo, the line should read:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc]='" &
Forms!frmFabDocCheckOff!cmboFabDoc & "'")

BeWyched said:
Hi

You can do this with just one line:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc='" &
Forms!frmFabDocCheckOff!cmboFabDoc) & "'"

This assumes that cmboFabDoc returns a string, otherwise leave out the
single 's. Note that,exagerated, '" is ' " and "'" is " ' ".

Cheers.
BW


Tom van Stiphout said:
On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP


Thanks....I will try it first thing tomorrow. Also What does the & "' " do
at the end?

:

Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


wrightlife11 wrote:
I am trying to get the earliest date of a set of records. I can't use last
or first because the records came into the database with no correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of the form
control it works fine, but I need to limit the parameters to the form control.
The form is open and the control has data like the value above. I tried to
use the results of the combo box since it is filtering records in the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate " & _
"FROM tblMain " & _
"WHERE (((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 
B

BeWyched

Hi Michel

You are quite right.

However, as generally the field providing the filter value will be on the
same form as the coding, using 's makes sense as 'me.' etc. can be used to
simplify the coding.
I'm not sure I agree with you re string " & ' values. I've tested my version
using, e.g., 2" and it works fine? VBA Help clearly shows that preferred best
practice is to build the filter string within the line, i.e. outside of the
"s.

Cheers.

BW


Michel Walsh said:
Nice catch, but if you just use DMin, you can keep the
FORMS!formName!controlName inside the string, which removes any kind of
problem with delimiter (and eventual presence of the delimiter itself inside
the value, such as if the value inside cmboFacDoc is 5'10 " 1/8 :


EarlyFlameOnShapes = DMin("SFStartDate",
"tblMain","FabDoc=Forms!frmFabDocCheckOff!cmboFabDoc")


The syntax FORMS!formName!controlName is nice as long as you use the user
interface, DoCmd, properties RowSource or RecordSource, and Dxxx functions.
The syntax is NOT automatically recognized with CurrentDb, but as long as it
IS recognized, keeping it inside the criteria string avoid the delimiter
problems.



Vanderghast, Access MVP


BeWyched said:
Sorry, a typo, the line should read:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc]='" &
Forms!frmFabDocCheckOff!cmboFabDoc & "'")

BeWyched said:
Hi

You can do this with just one line:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc='" &
Forms!frmFabDocCheckOff!cmboFabDoc) & "'"

This assumes that cmboFabDoc returns a string, otherwise leave out the
single 's. Note that,exagerated, '" is ' " and "'" is " ' ".

Cheers.
BW


:

On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP


Thanks....I will try it first thing tomorrow. Also What does the &
"' " do
at the end?

:

Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate "
& _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


wrightlife11 wrote:
I am trying to get the earliest date of a set of records. I
can't use last
or first because the records came into the database with no
correlation to
the dates.

I wrote this code to find the answer, but it has a problem getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'. Too
Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead of
the form
control it works fine, but I need to limit the parameters to the
form control.
The form is open and the control has data like the value above.
I tried to
use the results of the combo box since it is filtering records in
the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS StartDate
" & _
"FROM tblMain " & _
"WHERE
(((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

If the string hold and ' and ", as in the case of 5' 6" (five feet six
inches), you are due to double your delimiter inside the constant. It is a
little bit like:

? " 5' 6"" "
5' 6"


var="5' 6"" "
? "field=""" & var & """"
field= "5' 6" "


which is an invalid criteria ( having three double quotes ). A solution is
to use replace:

? "field =""" & Replace(var, """", """""") & """"
field ="5' 6"" "

which is now correct.


So,without the replace( ), if the delimiter is used in the content of the
variable, you get a problem and thus, it sounds much easier to let the
container inside the string, if possible:

"... WHERE field=FORMS!formName!controlNameHoldingTheValue5feet6inches"


but that assumes you use DoCmd, Dxxx, ... etc, but not CurrentDb.


Ken Getz reports using # as delimiter, in cases where " and ' can be used
inside the content of the variable, see ADH 2000 volume1, page 421.


Vanderghast, Access MVP


BeWyched said:
Hi Michel

You are quite right.

However, as generally the field providing the filter value will be on the
same form as the coding, using 's makes sense as 'me.' etc. can be used to
simplify the coding.
I'm not sure I agree with you re string " & ' values. I've tested my
version
using, e.g., 2" and it works fine? VBA Help clearly shows that preferred
best
practice is to build the filter string within the line, i.e. outside of
the
"s.

Cheers.

BW


Michel Walsh said:
Nice catch, but if you just use DMin, you can keep the
FORMS!formName!controlName inside the string, which removes any kind of
problem with delimiter (and eventual presence of the delimiter itself
inside
the value, such as if the value inside cmboFacDoc is 5'10 " 1/8 :


EarlyFlameOnShapes = DMin("SFStartDate",
"tblMain","FabDoc=Forms!frmFabDocCheckOff!cmboFabDoc")


The syntax FORMS!formName!controlName is nice as long as you use the
user
interface, DoCmd, properties RowSource or RecordSource, and Dxxx
functions.
The syntax is NOT automatically recognized with CurrentDb, but as long as
it
IS recognized, keeping it inside the criteria string avoid the delimiter
problems.



Vanderghast, Access MVP


BeWyched said:
Sorry, a typo, the line should read:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc]='" &
Forms!frmFabDocCheckOff!cmboFabDoc & "'")

:

Hi

You can do this with just one line:

EarlyFlameOnShapes = DMin("[SFStartDate]", "[tblMain]","[FabDoc='" &
Forms!frmFabDocCheckOff!cmboFabDoc) & "'"

This assumes that cmboFabDoc returns a string, otherwise leave out the
single 's. Note that,exagerated, '" is ' " and "'" is " ' ".

Cheers.
BW


:

On Tue, 9 Sep 2008 18:49:01 -0700, wrightlife11

The assumption is that Forms!frmFabDocCheckOff!cmboFabDoc returns a
string, and in a query a string value needs to be wrapped in
single-quotes. There is one just in front of it as well.

-Tom.
Microsoft Access MVP


Thanks....I will try it first thing tomorrow. Also What does the
&
"' " do
at the end?

:

Try the following

Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),#9/9/9999#,[SFStartDate])) AS StartDate
"
& _
"FROM tblMain " & _
"WHERE tblMain.FabDoc Like '" & _
Forms!frmFabDocCheckOff!cmboFabDoc & "' "


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


wrightlife11 wrote:
I am trying to get the earliest date of a set of records. I
can't use last
or first because the records came into the database with no
correlation to
the dates.

I wrote this code to find the answer, but it has a problem
getting
information from the form control that the SQL states.

Why does this code not work? I get a runtime error '3061'.
Too
Few
Parameters. Expected 1.
When I put a value( ie.'5132053') in the WHERE clause instead
of
the form
control it works fine, but I need to limit the parameters to
the
form control.
The form is open and the control has data like the value above.
I tried to
use the results of the combo box since it is filtering records
in
the data
set, but that did not work either.

Public Function EarlyFlameOnShapes() As Date

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dte As Variant


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblMain.*,
IIf(IsNull([SFStartDate]),'9/9/9999',[SFStartDate])) AS
StartDate
" & _
"FROM tblMain " & _
"WHERE
(((tblMain.FabDoc) Like " & _

"Forms!frmFabDocCheckOff!cmboFabDoc));")


rst.MoveFirst
dte = rst![StartDate]

While Not rst.EOF
dte = rst![StartDate]
If rst![StartDate] < dte Then
dte = rst![StartDate]
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing

EarlyFlameOnShapes = dte
End Function
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error "3061" Too few parameters. Expected 1. 4
Run-time error 3061. Too Few Parameters. Expected 2. 2
Run Query on Subform Using VBA Macro Button 1
Runtime Error 3021: 'No Current Record' 2
Use a string to define a field 1
Assign the records shown in a subform to a recordset 2
Code to fill Word form for multiple records from Access 1
Array compile error "Expected: As" 4

Top