If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Formula ?
Tom,
The code we have been discussing is a function that returns a certain value. I believe the modification I suggested before willl be necessary to get it to return the value you want. But here's the main point... when is this value being applied? So far we have seen the function that calculates the value, but we have not seen the procedure for how this is supposed to be inserted into the relevant field in a new record. Can you please let us know how this works? You mentioned a command button... what is the code on the Click event of that button? Thanks. -- Steve Schapel, Microsoft Access MVP Tom wrote: Quite the contrary. When I click on "new record" or use the button I created to do the same, the same number appears, ....001. It needs to be ....002. When I enter data into that record and click on new record, the number that needs to be there is ....003. The first part of the number is working fine. 051122xxx, the date part. It's the last part that is not incrementally changing as a new record is created. Tom |
#12
|
|||
|
|||
Formula ?
I'm going to shoot myself. Just kidding.
I recieved a Run-Time error '3075' Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE= '051122*". I have no idea what I'm doing, I'm plugging stuff in following along. I do not know any formulas, calculations or anything else for that matter. Somewhat familiar with excel and those formulas, not this however. Tom "John Spencer" wrote: Try trouble shooting? The lines you posted should all be on ONE line in the code - it appears that the newsreader may have wrapped them into two lines. If it is wrapping, then fix it to all be on one line or try the alternative formatting below Msgbox "Returned Value is " & _ StrCurrDate & _ Format(Val(Right(varHighValue,3))+1,"000") The idea behind the message box is to see what values are actually being returned at various stages? The other method of trouble shooting this code would be to step through the code a line at a time. IF the first MsgBox call returns "VarHighValue is " with no value, then you know that the DMAX is not returning anything and the conditional code is executing the True value of the test. If the second MsgBox call gets run, then you will know hopefully which value it is returning. If it doesn't get called then you know that VarHighValue is null and this bit of the code never executes. "Tom" wrote in message ... Syntax Error: Msgbox "Returned Value is " & StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000") remember, the date is working fine 051122 it is the last three numbers after the date that are not working. 051122001, next record 002, next record 003, etc Now what? tom SNIP |
#13
|
|||
|
|||
Formula ?
Don't shoot. Please!!
My fault. varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _ "[RUN NUMBER] Like '" & strCurrDate & "*'") I should not have left in the "=". My apologies for not double-checking the code. Tom wrote: I'm going to shoot myself. Just kidding. I recieved a Run-Time error '3075' Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE= '051122*". I have no idea what I'm doing, I'm plugging stuff in following along. I do not know any formulas, calculations or anything else for that matter. Somewhat familiar with excel and those formulas, not this however. Tom "John Spencer" wrote: Try trouble shooting? The lines you posted should all be on ONE line in the code - it appears that the newsreader may have wrapped them into two lines. If it is wrapping, then fix it to all be on one line or try the alternative formatting below Msgbox "Returned Value is " & _ StrCurrDate & _ Format(Val(Right(varHighValue,3))+1,"000") The idea behind the message box is to see what values are actually being returned at various stages? The other method of trouble shooting this code would be to step through the code a line at a time. IF the first MsgBox call returns "VarHighValue is " with no value, then you know that the DMAX is not returning anything and the conditional code is executing the True value of the test. If the second MsgBox call gets run, then you will know hopefully which value it is returning. If it doesn't get called then you know that VarHighValue is null and this bit of the code never executes. "Tom" wrote in message ... Syntax Error: Msgbox "Returned Value is " & StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000") remember, the date is working fine 051122 it is the last three numbers after the date that are not working. 051122001, next record 002, next record 003, etc Now what? tom SNIP |
#14
|
|||
|
|||
Formula ?
Alrighty.
No I get the message: varHighValue is with an "OK" button Now what do I do? Tom "John Spencer" wrote: Don't shoot. Please!! My fault. varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _ "[RUN NUMBER] Like '" & strCurrDate & "*'") I should not have left in the "=". My apologies for not double-checking the code. Tom wrote: I'm going to shoot myself. Just kidding. I recieved a Run-Time error '3075' Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE= '051122*". I have no idea what I'm doing, I'm plugging stuff in following along. I do not know any formulas, calculations or anything else for that matter. Somewhat familiar with excel and those formulas, not this however. Tom "John Spencer" wrote: Try trouble shooting? The lines you posted should all be on ONE line in the code - it appears that the newsreader may have wrapped them into two lines. If it is wrapping, then fix it to all be on one line or try the alternative formatting below Msgbox "Returned Value is " & _ StrCurrDate & _ Format(Val(Right(varHighValue,3))+1,"000") The idea behind the message box is to see what values are actually being returned at various stages? The other method of trouble shooting this code would be to step through the code a line at a time. IF the first MsgBox call returns "VarHighValue is " with no value, then you know that the DMAX is not returning anything and the conditional code is executing the True value of the test. If the second MsgBox call gets run, then you will know hopefully which value it is returning. If it doesn't get called then you know that VarHighValue is null and this bit of the code never executes. "Tom" wrote in message ... Syntax Error: Msgbox "Returned Value is " & StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000") remember, the date is working fine 051122 it is the last three numbers after the date that are not working. 051122001, next record 002, next record 003, etc Now what? tom SNIP |
#15
|
|||
|
|||
Formula ?
So, now you know that DMax function is not finding any match in ABLE_Table1.
Open that table. Is there a value in the table that should match? If so, what is it? If not, then I suspect that you aren't storing the newly created value in this table. Are you assigning the new value to a control on a form? Is that control's source set to the Run Number field in Able_Table1? "Tom" wrote in message ... Alrighty. No I get the message: varHighValue is with an "OK" button Now what do I do? Tom "John Spencer" wrote: Don't shoot. Please!! My fault. varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _ "[RUN NUMBER] Like '" & strCurrDate & "*'") I should not have left in the "=". My apologies for not double-checking the code. Tom wrote: I'm going to shoot myself. Just kidding. I recieved a Run-Time error '3075' Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE= '051122*". I have no idea what I'm doing, I'm plugging stuff in following along. I do not know any formulas, calculations or anything else for that matter. Somewhat familiar with excel and those formulas, not this however. Tom "John Spencer" wrote: Try trouble shooting? The lines you posted should all be on ONE line in the code - it appears that the newsreader may have wrapped them into two lines. If it is wrapping, then fix it to all be on one line or try the alternative formatting below Msgbox "Returned Value is " & _ StrCurrDate & _ Format(Val(Right(varHighValue,3))+1,"000") The idea behind the message box is to see what values are actually being returned at various stages? The other method of trouble shooting this code would be to step through the code a line at a time. IF the first MsgBox call returns "VarHighValue is " with no value, then you know that the DMAX is not returning anything and the conditional code is executing the True value of the test. If the second MsgBox call gets run, then you will know hopefully which value it is returning. If it doesn't get called then you know that VarHighValue is null and this bit of the code never executes. "Tom" wrote in message ... Syntax Error: Msgbox "Returned Value is " & StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000") remember, the date is working fine 051122 it is the last three numbers after the date that are not working. 051122001, next record 002, next record 003, etc Now what? tom SNIP |
#16
|
|||
|
|||
Formula ?
John Spencer wrote:
Open that table. Is there a value in the table that should match? If so, what is it? If not, then I suspect that you aren't storing the newly created value in this table. ... or that the function that calculates the value is being applied before the record is saved, so even if there is a record in the table, it could have been saved there after the processing of GetNextNumber(). This is the reason, Dev, why I suggested for you to post the code (which I can only asssume is on the Click event of your command button) that applies the new number. All we have seen is the function that calculates a value, but when and how that value is supposed to be applied to the database is the important aspect here. -- Steve Schapel, Microsoft Access MVP |
#17
|
|||
|
|||
Formula ?
Sorry, Tom, called you the wrong name... got mixed up with someone in
another thread! -- Steve Schapel, Microsoft Access MVP Steve Schapel wrote: This is the reason, Dev, why I suggested for you to post the code |
#18
|
|||
|
|||
Formula ?
when i enter the data into the record on the FORM,
it gives the number 51123001. I record it. Then I check the TABLE, and the number has been recorded as 51123001. When I go back the FORM, and right click on the control, then properties, the control source lists it as RUN NUMBER, not [ABLE_Table1]![RUN NUMBER]. So I tried changing it to that, and it gives me the same msg varHighValue is when I go to "next record" the number should not be 51123001 but 51123002. It is still 51123001. I've got no hair left. Tom "John Spencer" wrote: So, now you know that DMax function is not finding any match in ABLE_Table1. Open that table. Is there a value in the table that should match? If so, what is it? If not, then I suspect that you aren't storing the newly created value in this table. Are you assigning the new value to a control on a form? Is that control's source set to the Run Number field in Able_Table1? "Tom" wrote in message ... Alrighty. No I get the message: varHighValue is with an "OK" button Now what do I do? Tom "John Spencer" wrote: Don't shoot. Please!! My fault. varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _ "[RUN NUMBER] Like '" & strCurrDate & "*'") I should not have left in the "=". My apologies for not double-checking the code. Tom wrote: I'm going to shoot myself. Just kidding. I recieved a Run-Time error '3075' Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE= '051122*". I have no idea what I'm doing, I'm plugging stuff in following along. I do not know any formulas, calculations or anything else for that matter. Somewhat familiar with excel and those formulas, not this however. Tom "John Spencer" wrote: Try trouble shooting? The lines you posted should all be on ONE line in the code - it appears that the newsreader may have wrapped them into two lines. If it is wrapping, then fix it to all be on one line or try the alternative formatting below Msgbox "Returned Value is " & _ StrCurrDate & _ Format(Val(Right(varHighValue,3))+1,"000") The idea behind the message box is to see what values are actually being returned at various stages? The other method of trouble shooting this code would be to step through the code a line at a time. IF the first MsgBox call returns "VarHighValue is " with no value, then you know that the DMAX is not returning anything and the conditional code is executing the True value of the test. If the second MsgBox call gets run, then you will know hopefully which value it is returning. If it doesn't get called then you know that VarHighValue is null and this bit of the code never executes. "Tom" wrote in message ... Syntax Error: Msgbox "Returned Value is " & StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000") remember, the date is working fine 051122 it is the last three numbers after the date that are not working. 051122001, next record 002, next record 003, etc Now what? tom SNIP |
#19
|
|||
|
|||
Formula ?
Tom,
No, this won't work unless you have the leading 0 in the Run Number. varHighValue is looking in the table for a Run Number that starts with 051123 so if you enter Run Number as 51123001 this will not match. You will have to enter it as 051123001. -- Steve Schapel, Microsoft Access MVP Tom wrote: when i enter the data into the record on the FORM, it gives the number 51123001. I record it. Then I check the TABLE, and the number has been recorded as 51123001. When I go back the FORM, and right click on the control, then properties, the control source lists it as RUN NUMBER, not [ABLE_Table1]![RUN NUMBER]. So I tried changing it to that, and it gives me the same msg varHighValue is when I go to "next record" the number should not be 51123001 but 51123002. It is still 51123001. I've got no hair left. Tom |
#20
|
|||
|
|||
Formula ?
John,
That number is already set by the first part of the formula. It is automatic by yrmmdd. It only takes the last 2 digits of the year. By default, it is leaving off the "0". until 2010 then it would b be 101123 The date is changing fine, it's the last three numbers. So: To make it easy: I'm going to create another control, without a label on the FORM. place it next to the YYMMDD. That control will be the counter. starting at 001 So I need two things: 1. Formula for changing the date only in yymmdd format no "/"(without the 001) 2. Formula for changing the number, incrementally (max 999) always starting at 001 at 2400 hours. should appear on form as RUN NUMBER 51123 001 then, after I record an entry should read RUN NUMBER 51123 002 (next entry 003, etc. max 999) then, at midnight tonight should read RUN NUMBER 51124 001 That will make it a whole lot easier Tom "John Spencer" wrote: So, now you know that DMax function is not finding any match in ABLE_Table1. Open that table. Is there a value in the table that should match? If so, what is it? If not, then I suspect that you aren't storing the newly created value in this table. Are you assigning the new value to a control on a form? Is that control's source set to the Run Number field in Able_Table1? "Tom" wrote in message ... Alrighty. No I get the message: varHighValue is with an "OK" button Now what do I do? Tom "John Spencer" wrote: Don't shoot. Please!! My fault. varHighValue = DMax("[RUN NUMBER]", "ABLE_Table1", _ "[RUN NUMBER] Like '" & strCurrDate & "*'") I should not have left in the "=". My apologies for not double-checking the code. Tom wrote: I'm going to shoot myself. Just kidding. I recieved a Run-Time error '3075' Syntax error (missing operator) in query expression '[RUN NUMBER] LIKE= '051122*". I have no idea what I'm doing, I'm plugging stuff in following along. I do not know any formulas, calculations or anything else for that matter. Somewhat familiar with excel and those formulas, not this however. Tom "John Spencer" wrote: Try trouble shooting? The lines you posted should all be on ONE line in the code - it appears that the newsreader may have wrapped them into two lines. If it is wrapping, then fix it to all be on one line or try the alternative formatting below Msgbox "Returned Value is " & _ StrCurrDate & _ Format(Val(Right(varHighValue,3))+1,"000") The idea behind the message box is to see what values are actually being returned at various stages? The other method of trouble shooting this code would be to step through the code a line at a time. IF the first MsgBox call returns "VarHighValue is " with no value, then you know that the DMAX is not returning anything and the conditional code is executing the True value of the test. If the second MsgBox call gets run, then you will know hopefully which value it is returning. If it doesn't get called then you know that VarHighValue is null and this bit of the code never executes. "Tom" wrote in message ... Syntax Error: Msgbox "Returned Value is " & StrCurrDate & Format(Val(Right(varHighValue,3))+1,"000") remember, the date is working fine 051122 it is the last three numbers after the date that are not working. 051122001, next record 002, next record 003, etc Now what? tom SNIP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IF formula? | meris | Worksheet Functions | 1 | September 6th, 2005 07:14 AM |
adding row to forumla | carrera | General Discussion | 9 | August 23rd, 2005 10:24 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
generating formula with a formula | Lisa Linard | Worksheet Functions | 2 | November 27th, 2003 12:03 PM |