A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula ?



 
 
Thread Tools Display Modes
  #11  
Old November 22nd, 2005, 06:14 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 22nd, 2005, 11:30 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 04:07 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 10:39 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 02:04 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 06:41 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 06:46 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 07:55 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 08:27 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 09:19 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 01:03 PM


All times are GMT +1. The time now is 12:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.