Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: Expression not working

    Looking on Office.microsoft.com it would appear you can add an expression to connect to a table with a where condition. I keep getting an error with Name? in the feild

    [w1schpark] is the feild
    [Slacostsum] is the table
    [MID] is also a feild

    Example

    ID MID w1schpark
    1 January 168
    2 Feburary 168
    3 March 168

    (SELECT [W1SchPark] FROM [SlaCostSum] WHERE [MID] = "March")

    With this expression I get an error with a Name?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    although mid is not a reserved word it is a function in vba it cod cause problems.
    Aside from that the sql looks fine
    Where / how are you trying to use this?

  3. #3
    Join Date
    Feb 2013
    Posts
    99
    Changed the MID to MONJD and it did not resolved the issue

    I am createing an expression in the Control source of an unbound text box

    Thank you

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you exactly typed?
    im not certain you can assign an SQL expression directly to a control.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you may be able to do this by wrapping the sql into a function

    eg:=

    =Getwhatever("March")



    public function getwhatever(monthname)
    ' open a recordset
    ' or use dlookup
    returnt he value
    end function
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2013
    Posts
    99
    Thanks for the reply I did try putting a dlookup before the statment and this worked but I was not able to edit the data. Will try creating a function in vba when I get in this evening. Will it go in the on current events in the form properties thank you is there another function like dlookup which will enable editing

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by pjawynn View Post

    ...I was not able to edit the data...

    ...is there another function like dlookup which will enable editing...
    The DLookup has nothing to do with the Control not being editable; you can never edit a Control that has an expression in its Control Source.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Feb 2013
    Posts
    99

    Thanks for the information

    Thanks for letting me know maybe it is just easier to create a table for each month

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    aaaargh, please tell me you arent thinking of doing that........
    if its te same data over time, then it should be in the same table.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2013
    Posts
    99
    Would perfer not to but the hours may change each week and unless I can access the table to make alterations I cannot see a way to resolve the issue.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by pjawynn View Post

    ...maybe it is just easier to create a table for each month...
    You're talking about a spreadsheet, not a relational database!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Feb 2013
    Posts
    99
    Yes that would be the eaisest way but another part of the form uses expression to dcount the amount of parking warnings and fines each week which is all connected to the tables which are regular updated.

    This form is basically a monthly summary slit down in to weeks of employees hours each week and also it works out how many tickets and warnings are carried out during that week.

    So I cnnot split it into a seperate excel document.

    Thanks

Posting Permissions

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