 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2007GoToRecord Date Expression-Error 2505 Invalid Value
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 >I need some help with this expression; I am trying to open a datasheet >and goto a specific record: [quoted text clipped - 8 lines] > >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] 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 > Thanks for the response. It now gives me an "Error 2498: An > expression you entered is the wrong data type for one of the [quoted text clipped - 7 lines] > > 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.  SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com > > Thanks for the response. It now gives me an "Error 2498: An > > expression you entered is the wrong data type for one of the [quoted text clipped - 23 lines] > -- > Posted via a free Usenet account fromhttp://www.teranews.com 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 >> > Thanks for the response. It now gives me an "Error 2498: >> > An expression you entered is the wrong data type for one of [quoted text clipped - 37 lines] > > 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 SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com > >> > Thanks for the response. It now gives me an "Error 2498: > >> > An expression you entered is the wrong data type for one of [quoted text clipped - 58 lines] > > - Show quoted text - 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 >> > I am have a main form that has line item information. In >> > each line record, I have a subform that displays the cost [quoted text clipped - 42 lines] > > 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. , SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com > >> > I am have a main form that has line item information. In > >> > each line record, I have a subform that displays the cost [quoted text clipped - 58 lines] > > - Show quoted text - 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 >I need some help with the specific code, here's what I have so far: > [quoted text clipped - 8 lines] > >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] On Jun 11, 11:16 am, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > >I need some help with the specific code, here's what I have so far: > [quoted text clipped - 24 lines] > > 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 >Thanks for the response. It now gives me an "Error 2498: An >expression you entered is the wrong data type for one of the [quoted text clipped - 7 lines] > >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] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.