GoToRecord Date Expression-Error 2505 Invalid Value

[Todd H]

I need some help with this expression; I am trying to open a datasheet
and goto a specific record:

DoCmd.GoToRecord , , acGoTo, "BillingPeriodEndDate" = "Between" &
DateAdd("m", -3, Date) & "And" & DateAdd("m", -4, Date)

I think I am having some issues with the quotes in defining what I
need. BillingPeriodEndDate is a date.

Thanks,

Todd

 

[John W. Vinson]

I need some help with this expression; I am trying to open a datasheet
and goto a specific record:

DoCmd.GoToRecord , , acGoTo, "BillingPeriodEndDate" = "Between" &
DateAdd("m", -3, Date) & "And" & DateAdd("m", -4, Date)

I think I am having some issues with the quotes in defining what I
need. BillingPeriodEndDate is a date.

Thanks,

Todd

The BETWEEN operator and the = operator are different operators and need not
and should not be used together; and your quotes are all over the map. Your
string will evaluate to 0 (False, since the text strings
"BillingPeriodEndDate" and 03/08/2007And02/08/2007 are in fact unequal. Note
that blanks are meaningful!

You want the string to end up looking like

[BillingPeriodEndDate] BETWEEN #3/8/2007# AND #2/8/2007#

though I'd actually put the earlier date first. Try

"BillingPeriodEndDate Between #" & Format(DateAdd("m", -3, Date),
"mm/dd/yyyy") & "# And #" & DateAdd("m", -4, Date) & "#"

The # date delimiters will prevent Access from treating the date as a division
operation.

John W. Vinson [MVP]

 

[Todd H]

Thanks for the response. It now gives me an "Error 2498: An
expression you entered is the wrong data type for one of the
arguments"

Here's the entire statement:

DoCmd.GoToRecord , , acGoTo, "BillingPeriodEndDate Between #" &
Format(DateAdd("m", -4, Date), "mm/dd/yyyy") & "# And #" &
DateAdd("m", -3, Date) & "#"

Todd

 

[Bob Quintal]

Thanks for the response. It now gives me an "Error 2498: An
expression you entered is the wrong data type for one of the
arguments"

Here's the entire statement:

DoCmd.GoToRecord , , acGoTo, "BillingPeriodEndDate Between #" &
Format(DateAdd("m", -4, Date), "mm/dd/yyyy") & "# And #" &
DateAdd("m", -3, Date) & "#"

Todd

the GotoRecord expects a number that is the row number in the
recordset, for acGoTo, or an offset from the current row number,
for an acNext, acPrevious.

You would have to use one of the find methods to determine that
number.

 

[John W. Vinson]

Thanks for the response. It now gives me an "Error 2498: An
expression you entered is the wrong data type for one of the
arguments"

Here's the entire statement:

DoCmd.GoToRecord , , acGoTo, "BillingPeriodEndDate Between #" &
Format(DateAdd("m", -4, Date), "mm/dd/yyyy") & "# And #" &
DateAdd("m", -3, Date) & "#"

Todd

Hrm. I was so focused on the syntax errors in the WhereCondition that I didn't
even notice that you're trying to go to a record.

From the online help for GoToRecord:

Offset An integer or expression that evaluates to an integer. An expression
must be preceded by an equal sign (=).

This text string you have created is not an integer number. It's a text
string.

Could you step back a bit and explain what you are trying to accomplish? I am
guessing, but I don't want to give bad advice from guessing wrong!

John W. Vinson [MVP]

 

[Bob Quintal]

I am have a main form that has line item information. In each
line record, I have a subform that displays the cost
information by billingp period (i.e. end of the month). I am
trying to have the datasheet move to more current month (row)
so that I don't have to scroll down to get to the current
month in each record. Specifically, my billing periods began
in April, 2005, so now, in June, 2007, I am having to scroll
down in each record to input the cost information.

Thanks,

Todd

Ok, I see a quick solution and an elegant one.

Quick solution is to sort the billing periods in descending order,
the current one is at the top, oldest at the bottom, No scrolling
required, except to review history.

Elegant solution is to move to the bottom row, and then go back
your 3 months.
docmd.gotoRecord ,, aclast
docmd.gotorecord ,,acprevious, 3

 

[Todd H]

the GotoRecord expects a number that is the row number in the
recordset, for acGoTo, or an offset from the current row number,
for an acNext, acPrevious.

You would have to use one of the find methods to determine that
number.

I am have a main form that has line item information. In each line
record, I have a subform that displays the cost information by
billingp period (i.e. end of the month). I am trying to have the
datasheet move to more current month (row) so that I don't have to
scroll down to get to the current month in each record. Specifically,
my billing periods began in April, 2005, so now, in June, 2007, I am
having to scroll down in each record to input the cost information.

Thanks,

Todd

 

[Todd H]

Ok, I see a quick solution and an elegant one.

Quick solution is to sort the billing periods in descending order,
the current one is at the top, oldest at the bottom, No scrolling
required, except to review history.

Elegant solution is to move to the bottom row, and then go back
your 3 months.
docmd.gotoRecord ,, aclast
docmd.gotorecord ,,acprevious, 3

That does sound simple. Is it possible to put an expression in the
offset area so that it will change with each month. I have a total of
69 billing periods (which could possibly grow) and I can view about 20
rows in my form. I do have a BillingPeriodNo that corresponds to each
BillingPeriodEndDate. Maybe I should create a function to set the
"Current BillingPeriod No" and then offset from that. I am just not
too familar with variables.

Todd

 

[Bob Quintal]

That does sound simple. Is it possible to put an expression
in the offset area so that it will change with each month. I
have a total of 69 billing periods (which could possibly grow)
and I can view about 20 rows in my form. I do have a
BillingPeriodNo that corresponds to each BillingPeriodEndDate.
Maybe I should create a function to set the "Current
BillingPeriod No" and then offset from that. I am just not
too familar with variables.

Todd

yes, just declare the variable as type integer, calculate the
number of records to move to the variable and then change the
number to the name of the variable.

,

 

[Todd H]

yes, just declare the variable as type integer, calculate the
number of records to move to the variable and then change the
number to the name of the variable.

,

I need some help with the specific code, here's what I have so far:

Dim intCurrentPeriod As Integer
intCurrentPeriod = SELECT (BillingPeriodNumber)_
FROM tblBillingPeriod_
WHERE (((tblBillingPeriod.BillingPeriodEndDate)_
Between DateAdd("m",-4,Date()) And DateAdd("m",-3,Date())));

Me!frmPaymentSubform.SetFocus
DoCmd.GoToRecord , , acGoTo, intCurrentPeriod

I can't seem to get the sql statement to work.

Todd

 

[John W. Vinson]

I need some help with the specific code, here's what I have so far:

Dim intCurrentPeriod As Integer
intCurrentPeriod = SELECT (BillingPeriodNumber)_
FROM tblBillingPeriod_
WHERE (((tblBillingPeriod.BillingPeriodEndDate)_
Between DateAdd("m",-4,Date()) And DateAdd("m",-3,Date())));

Me!frmPaymentSubform.SetFocus
DoCmd.GoToRecord , , acGoTo, intCurrentPeriod

I can't seem to get the sql statement to work.

SQL is one language; VBA is a *different* language. Unlike some other
programming environments you can't just mix them; its als ich plotzlich auf
Deutsch... ooops, sorry, it's as if I suddenly switched to German.

If you're trying to find (a record? all records?) within that date range, then
you don't want to use the GoToRecord method AT ALL. Instead, set the form's
Filter property to a text string:

Me.Filter="tblBillingPeriod.BillingPeriodEndDate Between #" _
& DateAdd("m",-4,Date()) & "# And #" & DateAdd("m",-3,Date()) & "#"

or open the form's RecordsetClone and use FindFirst to locate the record with
that value.

John W. Vinson [MVP]

 

[Todd H]

SQL is one language; VBA is a *different* language. Unlike some other
programming environments you can't just mix them; its als ich plotzlich auf
Deutsch... ooops, sorry, it's as if I suddenly switched to German.

If you're trying to find (a record? all records?) within that date range, then
you don't want to use the GoToRecord method AT ALL. Instead, set the form's
Filter property to a text string:

Me.Filter="tblBillingPeriod.BillingPeriodEndDate Between #" _
& DateAdd("m",-4,Date()) & "# And #" & DateAdd("m",-3,Date()) & "#"

or open the form's RecordsetClone and use FindFirst to locate the record with
that value.

John W. Vinson [MVP]

I am trying to open a subform datasheet to a specific row for each
record in the main form; in this case, I have a subform with each row
representing cost informatoin by month. I am trying to set a variable
to the "current month" (which is actually 3 months ago, and then
"goto" that row when the main form opens. I am trying to use the sql
statement to return an integer value that will allow me to specify
which row to goto. I do need all the rows to display.

I have a BillingPeriodNumber (Primary key, integer) and a
BillingPeriodEndDate in the subform. I am trying to have the top row
in the datasheet display the "current month" so the sql statement will
only return one value.

Todd