Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Unanswered: Linking an expression to a table

    Dear forumers,

    I'm a beginner when it comes to Microsoft Access but i have a good general understanding of it. However, i'm faced with a new problem in an all new ball game for me. I currently have 2 tables
    -Deliveries
    -Production

    These 2 tables contain a field called "OF" (Fabrication Order).However the deliveries table has a modified version of "OF" called "ID/PO". I need to make the ID/PO coherent with the OF numbers, i already made the expression but this is where my SQL skills are lacking. I don't know how to link the expression to the table thus allowing to have numbers under the same format.
    Here is my expression
    OF: IIf(IsNumeric([7-15-29 Cmdes-délais]![ID/PO]);Abs(1*[7-15-29 Cmdes-délais]![ID/PO]);Mid((Left([7-15-29 Cmdes-délais]![ID/PO];Abs(InStr(1;[7-15-29 Cmdes-délais]![ID/PO];")")-1)));(Abs(InStr(1;[7-15-29 Cmdes-délais]![ID/PO];"("))+1)))

    (I'm working for a french company so the expression contains french functions, i am aware of the differences there are in French/English in Access & Excel but i can translate into french once i know what SQL syntax i should be using).

    Any help will be thoroughly appreciated !

  2. #2
    Join Date
    Mar 2015
    Posts
    27

    Cool Try this....

    Quote Originally Posted by Guy Winfield View Post
    Dear forumers,

    I'm a beginner when it comes to Microsoft Access but i have a good general understanding of it. However, i'm faced with a new problem in an all new ball game for me. I currently have 2 tables
    -Deliveries
    -Production

    These 2 tables contain a field called "OF" (Fabrication Order).However the deliveries table has a modified version of "OF" called "ID/PO". I need to make the ID/PO coherent with the OF numbers, i already made the expression but this is where my SQL skills are lacking. I don't know how to link the expression to the table thus allowing to have numbers under the same format.
    Here is my expression
    OF: IIf(IsNumeric([7-15-29 Cmdes-délais]![ID/PO]);Abs(1*[7-15-29 Cmdes-délais]![ID/PO]);Mid((Left([7-15-29 Cmdes-délais]![ID/PO];Abs(InStr(1;[7-15-29 Cmdes-délais]![ID/PO];")")-1)));(Abs(InStr(1;[7-15-29 Cmdes-délais]![ID/PO];"("))+1)))

    (I'm working for a french company so the expression contains french functions, i am aware of the differences there are in French/English in Access & Excel but i can translate into french once i know what SQL syntax i should be using).

    Any help will be thoroughly appreciated !
    I am assuming here that your function works though it looks odd to me, and without an example of an ID/PO to test against, I can't really test anything...

    Given that, what I would recommend is that you recode that function in a code module as a public function like so:

    Public GetOF(vOF_PO as Variant) as Variant
    GetOF = IIf(IsNumeric(vOF_PO);Abs(1*vOF_PO);Mid((Left(vOF_ PO;Abs(InStr(1;vOF_PO;")")-1)));(Abs(InStr(1;vID_PO;"("))+1)))
    End Function

    Then in your SQL, include a call to that public function like this:

    SELECT field1, field2, GetOF([7-15-29 Cmdes-délais]![ID/PO]) AS OF, field4, etc... FROM YourTable WHERE blahblahblah

    Note: The function can't be in a form module. It needs to be in a separate code module where it can be globally accessed anywhere else in the app and resolved by the SQL query engine (or whatever it is technically called).

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are better off enforcing these sort of rules using relational integrity. thats the R bit in RDBMS
    have a table which defines fabrication orders and then use the PK of that table (almost certainly the Fab Order itself) as the foreign key in the 'child' tables.

    dont' be temtped to roll up the fabrication order with other assorted crap, keep the fab order separate, add another column to make your 'modified' version of the QF/PO

    a word of caution you are probably OK with what you have got, but whoever designed this needs to understand why Microsoft publish a list of reserved symbols (symbols which should not be used to name tables, columns, controls, reports, forms and so on. as your application is working you are probably OK, in as much as Access can find a workaround, but just bear in mind that if you start getting weird errors and Access seems to sulk then it could be you've blown the system by using reserved words.

    its probably not practical to unpick what you have already done, but bear it in mind moving forward
    Last edited by healdem; 03-24-15 at 17:21.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2015
    Posts
    27
    Quote Originally Posted by healdem View Post
    you
    a word of caution you are probably OK with what you have got, but whoever designed this needs to understand why Microsoft publish a list of reserved symbols (symbols which should not be used to name tables, columns, controls, reports, forms and so on. as your application is working you are probably OK, in as much as Access can find a workaround, but just bear in mind that if you start getting weird errors and Access seems to sulk then it could be you've blown the system by using reserved words.
    I had that same concern at first about OF which I assume is the fieldname you are referring to, however it is not a reserved word in Access or the database engine.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Nope im referring to 7-15-29.... amongst others
    Im not aware ofbeing able to use a semi colon in place of a comma to separate parameters in a function call such as instr...
    i thought instr returned null if it couldnt find the specified symbol, if so you will need to handle nulls adequaradequately.

    As an aside for sample code provided for use by a third party there is a he k of a lot hping on in kne sentence/line. Im not criticising the code, far from it, but id suggest the OP, or other interested parties will learn more from a coding style which explains what its trying to do line by line. The days of trying to be as concise or terse as possible becuase if code page limits or processor execution speed on this dort of code should be long gone. Rolling up all thst functionality in a single line of code is fine if you are experienced but i sysuspect daunting for the ibexperienced, and a =&_/*€_ to debug

    ....just a thought.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2015
    Posts
    27

    Just a thought...

    Quote Originally Posted by healdem View Post
    Nope im referring to 7-15-29.... amongst others
    Im not aware ofbeing able to use a semi colon in place of a comma to separate parameters in a function call such as instr...
    i thought instr returned null if it couldnt find the specified symbol, if so you will need to handle nulls adequaradequately.

    As an aside for sample code provided for use by a third party there is a he k of a lot hping on in kne sentence/line. Im not criticising the code, far from it, but id suggest the OP, or other interested parties will learn more from a coding style which explains what its trying to do line by line. The days of trying to be as concise or terse as possible becuase if code page limits or processor execution speed on this dort of code should be long gone. Rolling up all thst functionality in a single line of code is fine if you are experienced but i sysuspect daunting for the ibexperienced, and a =&_/*€_ to debug

    ....just a thought.
    Yeah, I didn't like those table names much either. But I didn't design his tables or his function. And from the info in his post, he may not have either. He may be stuck working with what was already there. I also have my doubts about the function with all the semicolons but as I stated, without sample data I couldn't even try to test his function, which he states he has worked out... so I just showed the function he wrote with the table and field names replaced with a parameter. Making his function work will be up to him. I just demonstrated the method to link the function to an sql query which is what he asked how to do. I didn't try to fix or correct his function. That will be up to him.

    Speaking of daunting, do you ever preview what you write and try to correct the multitude of typos? I find reading your solutions to be a bit daunting, though I do appreciate your willingness to post your ideas/solutions. Just a thought.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ....Speaking of daunting, do you ever preview what you write and try to correct the multitude of typos? I find reading your solutions to be a bit daunting, though I do appreciate your willingness to post your ideas/solutions. Just a thought.

    Good point and well made.
    It is a problem with me. Not being defensive ( as if). I often use a tablet which seriously s rews up my response. In my minds eye I know what I have written even if in reality it appears differently
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2015
    Posts
    27

    Typing on a tablet

    Quote Originally Posted by healdem View Post
    Good point and well made.
    It is a problem with me. Not being defensive ( as if). I often use a tablet which seriously s rews up my response. In my minds eye I know what I have written even if in reality it appears differently
    Yeah, I know what you mean about typing on a tablet. I spend more time correcting than typing out my original thought. I hate typing on tablets and phones... it's the biggest reason I am not into texting or twitter. More pain than gain...

  9. #9
    Join Date
    Mar 2015
    Posts
    31

    Lightbulb Progress !

    After looking at all of your generous advice i tweaked around my expression a bit and it has resulted with this. I was given all of the tables & info so i can't do too much about the names unfortunately !

    diag anomalie: IIf(Left([17-40 Production]![Réclamation];2)="AC";"voir planning AC";IIf([Suivi DVI]![Act]="f00";"Atelier Rapide";IIf((IsNull([7-15-29 Cmdes & délais]![Fin Prévue]) And [7-15-29 Cmdes & délais]![StkDisp]=0 And [7-15-29 Cmdes & délais]![Délai]<Now());"A solder?";IIf([7-15-38 Livraisons]![Reste à livrer]<[7-15-29 Cmdes & délais]![StkDisp];"En Stock";IIf([7-15-29 Cmdes & délais]![StkDisp]>0;"Stock partiel";IIf([00- Extraction]![OF]<>[00- Extraction]![PremierDeOF];"Changement d'OF";IIf(Left([17-40 Production]![En Chg];2)="NC";"NC";IIf(Left([17-40 Production]![Réclamation];2)="MQ";"MQ";IIf([7-15-38 Livraisons]![Reste à livrer]>[17-40 Production]![QtéOF];"Qté OF insuffisant";"ok")))))))))

    Now the next issue is that once i try and run this through the query i receive thise message "Data type mismatch in criteria". I kind of feel like i'm making progress, i was wondering if it had something to do with the table properties ? Because there is a Date/Time value in my expression (in Red).

    Is there anyway i could give you more useful information maybe ?
    Here's an example of an ID/PO that i changed with an expression:
    (1030965)
    1231127
    +1273290
    (1127175)-

    And in OF format:
    1030965
    1231127
    1273290
    1127175
    Last edited by Guy Winfield; 03-25-15 at 11:51. Reason: More info

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    "data mismatch" usually means that you are supplying a text value in place of a numeric or date value
    or the text value isn't correctly delimited
    or you are comparing a date value from say the now() function to a string / text column in the db. incidentally do you want the current date and time or just the current date(). now() returns the current system date and time

    I take it the semi colon ( is an acceptable separator, in place of the usual comma (,) for parameters passed in functions in a French localised copy of Access?

    if you are trying to debug an IIF, especially a complex IIF then I'd strongly recommned that you do each element in stages
    get one bit to work, then add the next bit, prove that works and gradually build up to proven tested working code.

    I was given all of the tables & info so i can't do too much about the names unfortunately !
    which is fine, but bear it in mind going forward. if you get odd errors which dont' seem to be reasonable it could be down to (mis)using reserved word or symbol

    what I'd suggest you do is let the code halt then in debug mode work through each parameter, each function call and make certain the values are as you expected. to do that place the mouse cursor over each parameter.

    BTW there is absolutely no reason whatever to have spaces in a column name in Access, as you can set a caption as part of the column definition which will be used in forms and reports. I appreciate you have inherited this, but as said before bear it in mind going forward
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2015
    Posts
    31

    1 step forward 2 steps backward !

    What do you mean by curret date and time & current system date and time ? Whats the difference ?

    As for the semi colons you guessed it right, the french have to make things complicated !

    My boss used it in her database and it worked however its not the exact same as mine:
    diag anomalie: IIf(Left([Réclamation];2)="AC";"voir planning AC";IIf([Act]="f00";"Atelier Rapide";IIf((IsNull([Fin Prévue]) And [StkDisp]=0 And [En_Cours_Cdes_Forcaste]![Délai]<Now());"A solder?";IIf([Reste à livrer]<[StkDisp];"En Stock";IIf([StkDisp]>0;"Stock partiel";IIf([OF]<>[PremierDeOF];"Changement d'OF";IIf(Left([En Chg];2)="NC";"NC";IIf(Left([Réclamation];2)="MQ";"MQ";IIf([Reste à livrer]>[QtéOF];"Qté OF insuffisant";"ok")))))))))

    So i've started from the bottom and even this small expression won't work, same story about the criteria issue. Any ideas ?

    diag anomalie: IIf(Left([17-40 Production]![Réclamation],2)="AC","voir planning AC"

  12. #12
    Join Date
    Mar 2015
    Posts
    31
    Scratch the last part of my last comment, i've made a small breakthrough !

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...and the break through was.....?

    current system date is 25 March 2015
    current system date and time is 25 March 2015 1600*

    * or 25 Mars 2015 17:00 sur La France

    MS Access (or strictly speaking the underlying default db engine JET stors dates as a decimal number (the integr part is the nmumber fo days since 31st Dec 1899, the decimal part is the proportion of the day, so 0.5 is 12:00:00, but when you retrieve a datetime value it will be represented as a calendar date and time. if you dont' specify a time (the decimal component Access/JET stores the value as midnight on the specified day. that is somethign you do need to be aware of IF you have used NOW() to set date values, as now would return the current time AND date

    its not a major issue unless you want to compare values which do have a tiem element, so
    Code:
    if now() > '#2015/03/25#' then
      'do something
    else
      'do somethign else
    endif
    effectively this means that unless it happens to be midnight on the START of 25th March then the code would always do something else. the time element runs from 00:00:00 to 23:59:59

    As to how Access /JET handles different time zones I dunno.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •