i am trying to create a recordset in VBA. The current statement is:
rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
AhCalculationTime " & _
" FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
" WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
" ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
"Right([LogEntry],2));"
WHen I run the code, I get a Syntax Error(Missing Operator) from
TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
query is supposed to format a time based on those two values but the ":"
needs to be joined to it and not just inserted
(eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
do I fix this?

Thanks in advance.

Tirelle Lee

FIX:
1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
2-Save the whole thing as a query, open the query:
3-Use a bunch of string variables to help sort out the concatenations

I would do this:

Dim SQL as String
SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
& ':' & Right([LogEntry],2)) AS LogTime, "
SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
tblChargeData.Amps, tblChargeData.AhComputed, "
SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
& [LogTime] AS AhCalculationTime "
SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
JOIN tblChargeData "
SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
tblChargeData.VentLU) ON "
SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
[frmChargeEntry]))
SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"

'Debug.Print SQL

rst.Open SQL



This way you can copy/paste the SQL variable into a query to double
check the SQL.

Chris




On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> i am trying to create a recordset in VBA. The current statement is:
> rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> AhCalculationTime " & _
> " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> "Right([LogEntry],2));"
> WHen I run the code, I get a Syntax Error(Missing Operator) from
> TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> query is supposed to format a time based on those two values but the ":"
> needs to be joined to it and not just inserted
> (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> do I fix this?
>
> Thanks in advance.
>
> Tirelle Lee

Thanks Chris. Ok, I got past the previous error although I have not broken
it down by smaller pieces yet. Now I am getting this error:
-2147217904 No value given for one or more required parameters
Here is the code:
rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
: & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
AhCalculationTime " & _
" FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
" WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
" ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
Right([LogEntry],2));"

"(E-Mail Removed)" wrote:

> FIX:
> 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> 2-Save the whole thing as a query, open the query:
> 3-Use a bunch of string variables to help sort out the concatenations
>
> I would do this:
>
> Dim SQL as String
> SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> & ':' & Right([LogEntry],2)) AS LogTime, "
> SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> tblChargeData.Amps, tblChargeData.AhComputed, "
> SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> & [LogTime] AS AhCalculationTime "
> SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> JOIN tblChargeData "
> SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> tblChargeData.VentLU) ON "
> SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> [frmChargeEntry]))
> SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
>
> 'Debug.Print SQL
>
> rst.Open SQL
>
>
>
> This way you can copy/paste the SQL variable into a query to double
> check the SQL.
>
> Chris
>
>
>
>
> On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > i am trying to create a recordset in VBA. The current statement is:
> > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > AhCalculationTime " & _
> > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > "Right([LogEntry],2));"
> > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > query is supposed to format a time based on those two values but the ":"
> > needs to be joined to it and not just inserted
> > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > do I fix this?
> >
> > Thanks in advance.
> >
> > Tirelle Lee
>
>
>

I'm thinking the problem probably lies with:

WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))


When you use that in a query, it runs through MS Access first. Access
knows what FORMS!frmChargeEntry means. When you execute it in a
recordset, it runs through the ADO or DAO engine, which has no idea
waht it is. Try:

"WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
"


Chris


On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
wrote:
> Thanks Chris. Ok, I got past the previous error although I have not broken
> it down by smaller pieces yet. Now I am getting this error:
> -2147217904 No value given for one or more required parameters
> Here is the code:
> rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> AhCalculationTime " & _
> " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> Right([LogEntry],2));"
>
>
>
> "chris.nebin...@gmail.com" wrote:
> > FIX:
> > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > 2-Save the whole thing as a query, open the query:
> > 3-Use a bunch of string variables to help sort out the concatenations
>
> > I would do this:
>
> > Dim SQL as String
> > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > & ':' & Right([LogEntry],2)) AS LogTime, "
> > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > tblChargeData.Amps, tblChargeData.AhComputed, "
> > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > & [LogTime] AS AhCalculationTime "
> > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > JOIN tblChargeData "
> > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > tblChargeData.VentLU) ON "
> > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > [frmChargeEntry]))
> > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
>
> > 'Debug.Print SQL
>
> > rst.Open SQL
>
> > This way you can copy/paste the SQL variable into a query to double
> > check the SQL.
>
> > Chris
>
> > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > i am trying to create a recordset in VBA. The current statement is:
> > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > AhCalculationTime " & _
> > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > "Right([LogEntry],2));"
> > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > query is supposed to format a time based on those two values but the ":"
> > > needs to be joined to it and not just inserted
> > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > do I fix this?
>
> > > Thanks in advance.
>
> > > Tirelle Lee- Hide quoted text -
>
> - Show quoted text -

Thanks. Got a type mismatch for this?
"(E-Mail Removed)" wrote:

> I'm thinking the problem probably lies with:
>
> WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
>
>
> When you use that in a query, it runs through MS Access first. Access
> knows what FORMS!frmChargeEntry means. When you execute it in a
> recordset, it runs through the ADO or DAO engine, which has no idea
> waht it is. Try:
>
> "WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
> "
>
>
> Chris
>
>
> On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
> wrote:
> > Thanks Chris. Ok, I got past the previous error although I have not broken
> > it down by smaller pieces yet. Now I am getting this error:
> > -2147217904 No value given for one or more required parameters
> > Here is the code:
> > rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> > : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> > AhCalculationTime " & _
> > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> > Right([LogEntry],2));"
> >
> >
> >
> > "chris.nebin...@gmail.com" wrote:
> > > FIX:
> > > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > > 2-Save the whole thing as a query, open the query:
> > > 3-Use a bunch of string variables to help sort out the concatenations
> >
> > > I would do this:
> >
> > > Dim SQL as String
> > > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > > & ':' & Right([LogEntry],2)) AS LogTime, "
> > > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > > tblChargeData.Amps, tblChargeData.AhComputed, "
> > > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > > & [LogTime] AS AhCalculationTime "
> > > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > > JOIN tblChargeData "
> > > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > > tblChargeData.VentLU) ON "
> > > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > > [frmChargeEntry]))
> > > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
> >
> > > 'Debug.Print SQL
> >
> > > rst.Open SQL
> >
> > > This way you can copy/paste the SQL variable into a query to double
> > > check the SQL.
> >
> > > Chris
> >
> > > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > > i am trying to create a recordset in VBA. The current statement is:
> > > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > > AhCalculationTime " & _
> > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > > "Right([LogEntry],2));"
> > > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > > query is supposed to format a time based on those two values but the ":"
> > > > needs to be joined to it and not just inserted
> > > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > > do I fix this?
> >
> > > > Thanks in advance.
> >
> > > > Tirelle Lee- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

I made the assumption that ChargeID was a Integer. If it's a string,
then:

"WHERE (((tblChargeData.ChargeID)='" & [Forms]![frmChargeEntry] & "'))



Thats: = SINGLEQUOTE/DOUBLEQUOTE & .... & DOUBLEQUOTE/SINGLEQUOTE)




Chris

On Feb 21, 2:53 pm, Tirelle <Tire...@discussions.microsoft.com> wrote:
> Thanks. Got a type mismatch for this?
>
>
>
> "chris.nebin...@gmail.com" wrote:
> > I'm thinking the problem probably lies with:
>
> > WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
>
> > When you use that in a query, it runs through MS Access first. Access
> > knows what FORMS!frmChargeEntry means. When you execute it in a
> > recordset, it runs through the ADO or DAO engine, which has no idea
> > waht it is. Try:
>
> > "WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
> > "
>
> > Chris
>
> > On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
> > wrote:
> > > Thanks Chris. Ok, I got past the previous error although I have not broken
> > > it down by smaller pieces yet. Now I am getting this error:
> > > -2147217904 No value given for one or more required parameters
> > > Here is the code:
> > > rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> > > : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> > > AhCalculationTime " & _
> > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> > > Right([LogEntry],2));"
>
> > > "chris.nebin...@gmail.com" wrote:
> > > > FIX:
> > > > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > > > 2-Save the whole thing as a query, open the query:
> > > > 3-Use a bunch of string variables to help sort out the concatenations
>
> > > > I would do this:
>
> > > > Dim SQL as String
> > > > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > > > & ':' & Right([LogEntry],2)) AS LogTime, "
> > > > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > > > tblChargeData.Amps, tblChargeData.AhComputed, "
> > > > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > > > & [LogTime] AS AhCalculationTime "
> > > > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > > > JOIN tblChargeData "
> > > > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > > > tblChargeData.VentLU) ON "
> > > > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > > > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > > > [frmChargeEntry]))
> > > > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > > > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
>
> > > > 'Debug.Print SQL
>
> > > > rst.Open SQL
>
> > > > This way you can copy/paste the SQL variable into a query to double
> > > > check the SQL.
>
> > > > Chris
>
> > > > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > > > i am trying to create a recordset in VBA. The current statement is:
> > > > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > > > AhCalculationTime " & _
> > > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > > > "Right([LogEntry],2));"
> > > > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > > > query is supposed to format a time based on those two values but the ":"
> > > > > needs to be joined to it and not just inserted
> > > > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > > > do I fix this?
>
> > > > > Thanks in advance.
>
> > > > > Tirelle Lee- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

There was a problem with that line but it did not solve the issue.
It should have read
WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]!(ChargeNumber)))
Fixed that. Code is resolving it properly but still says I am missing
parameter.


"(E-Mail Removed)" wrote:

> I'm thinking the problem probably lies with:
>
> WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
>
>
> When you use that in a query, it runs through MS Access first. Access
> knows what FORMS!frmChargeEntry means. When you execute it in a
> recordset, it runs through the ADO or DAO engine, which has no idea
> waht it is. Try:
>
> "WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
> "
>
>
> Chris
>
>
> On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
> wrote:
> > Thanks Chris. Ok, I got past the previous error although I have not broken
> > it down by smaller pieces yet. Now I am getting this error:
> > -2147217904 No value given for one or more required parameters
> > Here is the code:
> > rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> > : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> > AhCalculationTime " & _
> > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> > Right([LogEntry],2));"
> >
> >
> >
> > "chris.nebin...@gmail.com" wrote:
> > > FIX:
> > > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > > 2-Save the whole thing as a query, open the query:
> > > 3-Use a bunch of string variables to help sort out the concatenations
> >
> > > I would do this:
> >
> > > Dim SQL as String
> > > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > > & ':' & Right([LogEntry],2)) AS LogTime, "
> > > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > > tblChargeData.Amps, tblChargeData.AhComputed, "
> > > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > > & [LogTime] AS AhCalculationTime "
> > > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > > JOIN tblChargeData "
> > > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > > tblChargeData.VentLU) ON "
> > > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > > [frmChargeEntry]))
> > > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
> >
> > > 'Debug.Print SQL
> >
> > > rst.Open SQL
> >
> > > This way you can copy/paste the SQL variable into a query to double
> > > check the SQL.
> >
> > > Chris
> >
> > > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > > i am trying to create a recordset in VBA. The current statement is:
> > > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > > AhCalculationTime " & _
> > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > > "Right([LogEntry],2));"
> > > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > > query is supposed to format a time based on those two values but the ":"
> > > > needs to be joined to it and not just inserted
> > > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > > do I fix this?
> >
> > > > Thanks in advance.
> >
> > > > Tirelle Lee- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

Okay, we're getting there...

(You know, this whole thing would be easier to put the code into a
saved query and just open the query...)

You need to evaluate the value of the ChargeNumber control in VBA
before passing it into the SQL query:

If ChargeID is a number:

"Where tblChargeData.ChargeID = " & Forms!frmChargeEntry!CHargeNumber


If it's a text field:


"Where tblChargeData.ChargeID = '" & Forms!frmChargeEntry!
CHargeNumber & "'"



Chris

On Feb 21, 3:10 pm, Tirelle <Tire...@discussions.microsoft.com> wrote:
> There was a problem with that line but it did not solve the issue.
> It should have read
> WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]!(ChargeNumber)))
> Fixed that. Code is resolving it properly but still says I am missing
> parameter.
>
>
>
> "chris.nebin...@gmail.com" wrote:
> > I'm thinking the problem probably lies with:
>
> > WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
>
> > When you use that in a query, it runs through MS Access first. Access
> > knows what FORMS!frmChargeEntry means. When you execute it in a
> > recordset, it runs through the ADO or DAO engine, which has no idea
> > waht it is. Try:
>
> > "WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
> > "
>
> > Chris
>
> > On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
> > wrote:
> > > Thanks Chris. Ok, I got past the previous error although I have not broken
> > > it down by smaller pieces yet. Now I am getting this error:
> > > -2147217904 No value given for one or more required parameters
> > > Here is the code:
> > > rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> > > : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> > > AhCalculationTime " & _
> > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> > > Right([LogEntry],2));"
>
> > > "chris.nebin...@gmail.com" wrote:
> > > > FIX:
> > > > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > > > 2-Save the whole thing as a query, open the query:
> > > > 3-Use a bunch of string variables to help sort out the concatenations
>
> > > > I would do this:
>
> > > > Dim SQL as String
> > > > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > > > & ':' & Right([LogEntry],2)) AS LogTime, "
> > > > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > > > tblChargeData.Amps, tblChargeData.AhComputed, "
> > > > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > > > & [LogTime] AS AhCalculationTime "
> > > > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > > > JOIN tblChargeData "
> > > > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > > > tblChargeData.VentLU) ON "
> > > > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > > > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > > > [frmChargeEntry]))
> > > > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > > > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
>
> > > > 'Debug.Print SQL
>
> > > > rst.Open SQL
>
> > > > This way you can copy/paste the SQL variable into a query to double
> > > > check the SQL.
>
> > > > Chris
>
> > > > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > > > i am trying to create a recordset in VBA. The current statement is:
> > > > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > > > AhCalculationTime " & _
> > > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > > > "Right([LogEntry],2));"
> > > > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > > > query is supposed to format a time based on those two values but the ":"
> > > > > needs to be joined to it and not just inserted
> > > > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > > > do I fix this?
>
> > > > > Thanks in advance.
>
> > > > > Tirelle Lee- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I did try that using the query to filter based on Chargenumber of open form.
Query works fine by itself, but when i try to create a recordset using it I
get the following error:
No value given for one or more required parameters

Here is code:
rst.Open "qryCalculateAmpHrs", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdTable

Once create, I need to be able to update and edit data systemamtically.

"(E-Mail Removed)" wrote:

> Okay, we're getting there...
>
> (You know, this whole thing would be easier to put the code into a
> saved query and just open the query...)
>
> You need to evaluate the value of the ChargeNumber control in VBA
> before passing it into the SQL query:
>
> If ChargeID is a number:
>
> "Where tblChargeData.ChargeID = " & Forms!frmChargeEntry!CHargeNumber
>
>
> If it's a text field:
>
>
> "Where tblChargeData.ChargeID = '" & Forms!frmChargeEntry!
> CHargeNumber & "'"
>
>
>
> Chris
>
> On Feb 21, 3:10 pm, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > There was a problem with that line but it did not solve the issue.
> > It should have read
> > WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]!(ChargeNumber)))
> > Fixed that. Code is resolving it properly but still says I am missing
> > parameter.
> >
> >
> >
> > "chris.nebin...@gmail.com" wrote:
> > > I'm thinking the problem probably lies with:
> >
> > > WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
> >
> > > When you use that in a query, it runs through MS Access first. Access
> > > knows what FORMS!frmChargeEntry means. When you execute it in a
> > > recordset, it runs through the ADO or DAO engine, which has no idea
> > > waht it is. Try:
> >
> > > "WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
> > > "
> >
> > > Chris
> >
> > > On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
> > > wrote:
> > > > Thanks Chris. Ok, I got past the previous error although I have not broken
> > > > it down by smaller pieces yet. Now I am getting this error:
> > > > -2147217904 No value given for one or more required parameters
> > > > Here is the code:
> > > > rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> > > > : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> > > > AhCalculationTime " & _
> > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> > > > Right([LogEntry],2));"
> >
> > > > "chris.nebin...@gmail.com" wrote:
> > > > > FIX:
> > > > > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > > > > 2-Save the whole thing as a query, open the query:
> > > > > 3-Use a bunch of string variables to help sort out the concatenations
> >
> > > > > I would do this:
> >
> > > > > Dim SQL as String
> > > > > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > > > > & ':' & Right([LogEntry],2)) AS LogTime, "
> > > > > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > > > > tblChargeData.Amps, tblChargeData.AhComputed, "
> > > > > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > > > > & [LogTime] AS AhCalculationTime "
> > > > > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > > > > JOIN tblChargeData "
> > > > > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > > > > tblChargeData.VentLU) ON "
> > > > > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > > > > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > > > > [frmChargeEntry]))
> > > > > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > > > > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
> >
> > > > > 'Debug.Print SQL
> >
> > > > > rst.Open SQL
> >
> > > > > This way you can copy/paste the SQL variable into a query to double
> > > > > check the SQL.
> >
> > > > > Chris
> >
> > > > > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > > > > i am trying to create a recordset in VBA. The current statement is:
> > > > > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > > > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > > > > AhCalculationTime " & _
> > > > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > > > > "Right([LogEntry],2));"
> > > > > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > > > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > > > > query is supposed to format a time based on those two values but the ":"
> > > > > > needs to be joined to it and not just inserted
> > > > > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > > > > do I fix this?
> >
> > > > > > Thanks in advance.
> >
> > > > > > Tirelle Lee- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

What happens when you open the query directly without using code?
Does it open? If so, then it is tripping on the Forms!frmChargeEntry!
ChargeNumber. You can use a Command to handle this:


Dim rst As ADODB.Recordset
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "query1"
cmd.CommandType = adCmdTable
cmd.Parameters(0).Value = Eval(cmd.Parameters(0).Name)
Set rst = cmd.Execute
Do Until rst.EOF
Debug.Print rst.Fields(0).Value
rst.MoveNext
Loop



Chris


On Feb 21, 3:43 pm, Tirelle <Tire...@discussions.microsoft.com> wrote:
> I did try that using the query to filter based on Chargenumber of open form.
> Query works fine by itself, but when i try to create a recordset using it I
> get the following error:
> No value given for one or more required parameters
>
> Here is code:
> rst.Open "qryCalculateAmpHrs", CurrentProject.Connection, adOpenDynamic,
> adLockOptimistic, adCmdTable
>
> Once create, I need to be able to update and edit data systemamtically.
>
>
>
> "chris.nebin...@gmail.com" wrote:
> > Okay, we're getting there...
>
> > (You know, this whole thing would be easier to put the code into a
> > saved query and just open the query...)
>
> > You need to evaluate the value of the ChargeNumber control in VBA
> > before passing it into the SQL query:
>
> > If ChargeID is a number:
>
> > "Where tblChargeData.ChargeID = " & Forms!frmChargeEntry!CHargeNumber
>
> > If it's a text field:
>
> > "Where tblChargeData.ChargeID = '" & Forms!frmChargeEntry!
> > CHargeNumber & "'"
>
> > Chris
>
> > On Feb 21, 3:10 pm, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > There was a problem with that line but it did not solve the issue.
> > > It should have read
> > > WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]!(ChargeNumber)))
> > > Fixed that. Code is resolving it properly but still says I am missing
> > > parameter.
>
> > > "chris.nebin...@gmail.com" wrote:
> > > > I'm thinking the problem probably lies with:
>
> > > > WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
>
> > > > When you use that in a query, it runs through MS Access first. Access
> > > > knows what FORMS!frmChargeEntry means. When you execute it in a
> > > > recordset, it runs through the ADO or DAO engine, which has no idea
> > > > waht it is. Try:
>
> > > > "WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
> > > > "
>
> > > > Chris
>
> > > > On Feb 21, 12:28 pm, Tirelle <Tire...@discussions.microsoft.com>
> > > > wrote:
> > > > > Thanks Chris. Ok, I got past the previous error although I have not broken
> > > > > it down by smaller pieces yet. Now I am getting this error:
> > > > > -2147217904 No value given for one or more required parameters
> > > > > Here is the code:
> > > > > rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
> > > > > : & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
> > > > > AhCalculationTime " & _
> > > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
> > > > > Right([LogEntry],2));"
>
> > > > > "chris.nebin...@gmail.com" wrote:
> > > > > > FIX:
> > > > > > 1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
> > > > > > 2-Save the whole thing as a query, open the query:
> > > > > > 3-Use a bunch of string variables to help sort out the concatenations
>
> > > > > > I would do this:
>
> > > > > > Dim SQL as String
> > > > > > SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
> > > > > > & ':' & Right([LogEntry],2)) AS LogTime, "
> > > > > > SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
> > > > > > tblChargeData.Amps, tblChargeData.AhComputed, "
> > > > > > SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
> > > > > > & [LogTime] AS AhCalculationTime "
> > > > > > SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
> > > > > > JOIN tblChargeData "
> > > > > > SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
> > > > > > tblChargeData.VentLU) ON "
> > > > > > SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
> > > > > > SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
> > > > > > [frmChargeEntry]))
> > > > > > SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
> > > > > > TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
>
> > > > > > 'Debug.Print SQL
>
> > > > > > rst.Open SQL
>
> > > > > > This way you can copy/paste the SQL variable into a query to double
> > > > > > check the SQL.
>
> > > > > > Chris
>
> > > > > > On Feb 21, 8:41 am, Tirelle <Tire...@discussions.microsoft.com> wrote:
> > > > > > > i am trying to create a recordset in VBA. The current statement is:
> > > > > > > rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
> > > > > > > ":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
> > > > > > > tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
> > > > > > > tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
> > > > > > > AhCalculationTime " & _
> > > > > > > " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
> > > > > > > ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
> > > > > > > tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
> > > > > > > " WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
> > > > > > > " ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
> > > > > > > "Right([LogEntry],2));"
> > > > > > > WHen I run the code, I get a Syntax Error(Missing Operator) from
> > > > > > > TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
> > > > > > > query is supposed to format a time based on those two values but the ":"
> > > > > > > needs to be joined to it and not just inserted
> > > > > > > (eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
> > > > > > > do I fix this?
>
> > > > > > > Thanks in advance.
>
> > > > > > > Tirelle Lee- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -