Where is my codecerror?

[Nova]

This is my code in btnSearch_click event


Dim Cnx As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim StrRst As String
Dim N As Integer


StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=Forms!MatTurnoverF.StartDate);"

Set Cnx = CurrentProject.Connection
Set Rst = New ADODB.Recordset


Rst.Open StrRst, Cnx, adOpenForwardOnly, adLockReadOnly, 1

It show message

No value given for one or morerequired parameter

If I copy Strrst to create query, it work correct. I don't understand where
am I wrong?

 

[John Spencer]

When working in VBA the reference to the control on a form is NOT recognized.
The simplest was to handle the situation is to grab the value of the control
and concatenate it into the query string.

IF IsDate(Forms!MatTurnoverF!StartDate) Then
StrRst = "SELECT * FROM MatStock WHERE [ActionDate]=" &
FORMAT(Forms!MatTurnoverF!StartDate,"\#yyyy-mm-dd\#")

'Your other code here
Else
Msgbox "Please enter a valid Start Date."
End If


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

 

[Tom van Stiphout]

On Mon, 11 Jan 2010 02:50:01 -0800, Nova

I would write that as:
StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

or better still, put the code in a query.

-Tom.
Microsoft Access MVP

 

[Nova]

I try it many times but it still has some error

StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

I think may be mydate is Thailand format not US format
Then I change code to

StrRst = "SELECT * FROM MatStock WHERE
(Format([ActionDate],""mmm yy"")=Format(#" & Forms!MatTurnoverF!StartDate &
"#,""mmm yy"");"
also I change ate time format in regional setting to Thai,The result is no
error like before but The Rst is no any record

then I try change coe to

(Format([ActionDate],""mmm yy"") Between
Format(#" & Forms!MatTurnoverF!StartDate & "#,""mmm yy"")
AND Format(#" & Forms!MatTurnoverF!EndDate & "#,""mmm yy""))

It still no error but the recordset from Rst is not correct, some recoed is
outsie startdate and enddate, some record between startdate and enddate is
not shown
How should I do?

"Tom van Stiphout" เขียน:

On Mon, 11 Jan 2010 02:50:01 -0800, Nova

I would write that as:
StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

or better still, put the code in a query.

-Tom.
Microsoft Access MVP

This is my code in btnSearch_click event


Dim Cnx As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim StrRst As String
Dim N As Integer


StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=Forms!MatTurnoverF.StartDate);"

Set Cnx = CurrentProject.Connection
Set Rst = New ADODB.Recordset


Rst.Open StrRst, Cnx, adOpenForwardOnly, adLockReadOnly, 1

It show message

No value given for one or morerequired parameter

If I copy Strrst to create query, it work correct. I don't understand where
am I wrong?

.

 

[John W. Vinson]

I try it many times but it still has some error

StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

I think may be mydate is Thailand format not US format
Then I change code to

What is Thailand format? Could you post today's date as an example?

StrRst = "SELECT * FROM MatStock WHERE
(Format([ActionDate],""mmm yy"")=Format(#" & Forms!MatTurnoverF!StartDate &
"#,""mmm yy"");"

mmm yy is not a date: it must be a complete date. In addition, the Format()
function returns a text string, and your # is converting the criterion to a
Date/Time.

Try

strRST = "SELECT * FROM MatStock WHERE ActionDate = #" & _
Format(CDate([Forms]![MatTurnoverF!StartDate), "yyyy-mm-dd")) & "#"

 

[Nova]

I try as you reccommend but it still error
Thai format similar US format but the year is 543 B.C.
Today is 14 Thaimonth 2553

then I try
Rst.Open "MatStock", Cnx, adOpenForwardOnly, adLockReadOnly, -1
N=0
With Rst
do until .EOF
If StartDate = .Fields!ActionDate.Value Then

MgBox .Fields!ActionDate.Value
N=N+1
End If
.MoveNext
Loop
End With
Msgbox N
Rst.close
Set Rst = Nothing

It works correct but this must check all records in table "Matstock"
Please help me again


"John W. Vinson" เขียน:

I try it many times but it still has some error

StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

I think may be mydate is Thailand format not US format
Then I change code to

What is Thailand format? Could you post today's date as an example?

StrRst = "SELECT * FROM MatStock WHERE
(Format([ActionDate],""mmm yy"")=Format(#" & Forms!MatTurnoverF!StartDate &
"#,""mmm yy"");"

mmm yy is not a date: it must be a complete date. In addition, the Format()
function returns a text string, and your # is converting the criterion to a
Date/Time.

Try

strRST = "SELECT * FROM MatStock WHERE ActionDate = #" & _
Format(CDate([Forms]![MatTurnoverF!StartDate), "yyyy-mm-dd")) & "#"

 

[Nova]

Thaimonth is month in thai language (Like Jan,Feb,....,Dec in Thai language)

I try

StrRst = "SELECT * FROM Matstock WHERE (Format([ActionDate], ""dd mmm yy"")
=Format('" & [Forms]![MatTurnoverF].[StartDate] & "', ""dd mmm yy""))"

It show me correct record

and then I change from = to between

StrRst = "SELECT * FROM Matstock WHERE Format([ACtionDate], ""dd mm yyyy"")
Between Format(('" & [Forms]![MatTurnoverF].[StartDate] & "' ), ""dd mm
yyyy"") "
AND Format(('" & [Forms]![MatTurnoverF].[EndDate] & "'), ""dd mm yyyy"")"

The result is not correct again.

field Actiondate in table "Matstock" is set format to dd mm yy
and in form "MaturnoverF", Both Startdate and EndDate combobox is set format
to dd mm yy


"John W. Vinson" เขียน:

 

[Nova]

Thanks for all help



"KenSheridan via AccessMonster.com" เขียน:

That won't work because the 'dd mm yyyy' format does not sort correctly.
Using the same approach try this:

StrRst = "SELECT * FROM Matstock WHERE " & _
"Format([ActionDate], ""yyyymmdd"") Between " & _
Format([Forms]![MatTurnoverF].[StartDate], "yyyymmdd") & " AND " & _
Format([Forms]![MatTurnoverF].[EndDate], "yyyymmdd")

Check first in the debug window that formatting a date entered as a string in
Thai date format does give you the correct date in yyyymmdd format by
entering:

? Format("14 makarakhom 2553","yyyymmdd")

which should give 20100114

Hope I got the month right!

Ken Sheridan
Stafford, England

Thaimonth is month in thai language (Like Jan,Feb,....,Dec in Thai language)

I try

StrRst = "SELECT * FROM Matstock WHERE (Format([ActionDate], ""dd mmm yy"")
=Format('" & [Forms]![MatTurnoverF].[StartDate] & "', ""dd mmm yy""))"

It show me correct record

and then I change from = to between

StrRst = "SELECT * FROM Matstock WHERE Format([ACtionDate], ""dd mm yyyy"")
Between Format(('" & [Forms]![MatTurnoverF].[StartDate] & "' ), ""dd mm
yyyy"") "
AND Format(('" & [Forms]![MatTurnoverF].[EndDate] & "'), ""dd mm yyyy"")"

The result is not correct again.

field Actiondate in table "Matstock" is set format to dd mm yy
and in form "MaturnoverF", Both Startdate and EndDate combobox is set format
to dd mm yy

"John W. Vinson" เขียน:

I try as you reccommend but it still error
Thai format similar US format but the year is 543 B.C.

[quoted text clipped - 34 lines]

Perhaps you could explain some more about the context. What's in the table?
What are you trying to accomplish?