none
Dlookup Syntax help RRS feed

  • Question

  • I have an Access form with the following named fields on it.  All fields Are Long Integers.

    NoPartsProduced

    AQL

    SampleSize

     I have a table, Part of which I have reproduced below.

    I need to write a dlookup statement to return the Sample size from the above table.  I know the NoPartsProduced, which will be between the LotSizeUpper and the Lot size Lower for the AQL Level desired.

     

    So, for example if the desired AQL is 4 and the number of parts produced is 100, the sample size would be 9.

    I think what I need to do is write a statement to return the sample size when the AQL is equal to 4 and the NoOfParts is between lotsizeuppr and lotsize lower, but for the life of me, cannot figure out the correct syntax!  Anyone help?

    I don't want to write a query since there are several forms and reports this will be used on, and I don't want to write a query to return this value for every form and report

    Wednesday, January 10, 2018 8:10 PM

Answers

  • I think what I need to do is write a statement to return the sample size when the AQL is equal to 4 and the NoOfParts is between lotsizeuppr and lotsize lower, but for the life of me, cannot figure out the correct syntax!  Anyone help?

    Hi tkosel,

    First, I do not believe the field AQL is a long. I assume it is Single.

    You could use a small function Get_samplesize. You have to fill in the real name of the table for <SizeTable>.

    Function Get_samplesize(AQL As Single, Lotsize As Long)
      Dim cur_sql As String
      
      cur_sql = "SELECT SampleSize FROM <SizeTable>" _
              & " WHERE AQL = " & AQL _
              & " AND LotSizeLower <= " & Lotsize _
              & " AND LotSizeUpper >= " & Lotsize
      Get_samplesize = CurrentDb.OpenRecordset(cur_sql)!SampleSize
    
    End Function
    

    In this way you can easily make all your own "DLookUp's".

    The code is not tested, but you the general approach should be clear.

    Imb.

    • Marked as answer by tkosel Wednesday, January 10, 2018 10:08 PM
    Wednesday, January 10, 2018 8:47 PM
  • Do you get an error with the following version?

    =DLookup("SampleSize","AQLTableParent","AQL=" & [DesiredAQL] & " AND " & [NumberOfPartsProduced] & " Between LotSizeLower And LotSizeupper")

    Make sure [DesiredAQL] and [NumberOfPartsProduced] are the names of the Textboxes.

    Hope it helps...

    • Marked as answer by tkosel Wednesday, January 10, 2018 10:08 PM
    Wednesday, January 10, 2018 9:56 PM

All replies

  • Hi,

    How about maybe something like:

    DLookup("SampleSize", "TableName", "AQL=4 AND 100 Between LostSizeLower And LotSizeUpper")

    If 4 and 100 are going to be variables, then make sure to concatenate them into the expression.

    Hope it helps...

    Wednesday, January 10, 2018 8:41 PM
  • I think what I need to do is write a statement to return the sample size when the AQL is equal to 4 and the NoOfParts is between lotsizeuppr and lotsize lower, but for the life of me, cannot figure out the correct syntax!  Anyone help?

    Hi tkosel,

    First, I do not believe the field AQL is a long. I assume it is Single.

    You could use a small function Get_samplesize. You have to fill in the real name of the table for <SizeTable>.

    Function Get_samplesize(AQL As Single, Lotsize As Long)
      Dim cur_sql As String
      
      cur_sql = "SELECT SampleSize FROM <SizeTable>" _
              & " WHERE AQL = " & AQL _
              & " AND LotSizeLower <= " & Lotsize _
              & " AND LotSizeUpper >= " & Lotsize
      Get_samplesize = CurrentDb.OpenRecordset(cur_sql)!SampleSize
    
    End Function
    

    In this way you can easily make all your own "DLookUp's".

    The code is not tested, but you the general approach should be clear.

    Imb.

    • Marked as answer by tkosel Wednesday, January 10, 2018 10:08 PM
    Wednesday, January 10, 2018 8:47 PM
  • DBGuy,

    As usual, thanks for your prompt response.

    A control source of

    =DLookUp("SampleSize","AQLTableParent","AQL=4 AND 100 Between LostSizeLower And LotSizeUpper")

    Produces an error

    Also, the 4 is in a text box control named "DesiredAQL" on the current form and the 100 is in a text box control  control named "NumberOfPartsProduced" on the current form.

    Wednesday, January 10, 2018 9:12 PM
  • Sorry, I had a typo in the previous expression: LostSizeLower. "Lost" rather than "Lot"

    Also try:

    =DLookup("SampleSize","AQLTableParent","AQL=" & [DesiredAQL] & " AND [LotSizeLower]>=" & [NumberOfPartsProduced] & " AND [LotSizeUpper]<=" & [NumberOfPartsProduced])

    Hope it helps...

    Wednesday, January 10, 2018 9:21 PM
  • I should have caught that too!  Anyhow, we are making progress.

    =DLookUp("SampleSize","AQLTableParent","AQL=1 AND 100 Between LotSizeLower And LotSizeUpper")

    Now works as expected however,

    =DLookup("SampleSize","AQLTableParent","AQL=" & [DesiredAQL] & " AND [LotSizeLower]>=" & [NumberOfPartsProduced] & " AND [LotSizeUpper]<=" & [NumberOfPartsProduced])

    Still gives an error.

    Wednesday, January 10, 2018 9:52 PM
  • Do you get an error with the following version?

    =DLookup("SampleSize","AQLTableParent","AQL=" & [DesiredAQL] & " AND " & [NumberOfPartsProduced] & " Between LotSizeLower And LotSizeupper")

    Make sure [DesiredAQL] and [NumberOfPartsProduced] are the names of the Textboxes.

    Hope it helps...

    • Marked as answer by tkosel Wednesday, January 10, 2018 10:08 PM
    Wednesday, January 10, 2018 9:56 PM
  • DbGuy,

    Its Leo right?

    Thanks, that doesn't yield an error and returns the right value.  As usual, you have been a great help.  I have some other kinks to iron out.  This project is one that has gotten out of hand.  They keep adding enhancement requests that are hard to add, because provisions were not made to accommodate them in the initial design.  Bet that has never happened to you. 

    I have a lot of trouble when it comes to "", [],& symbols and their proper usage along with between and AND operators.  I need more experience.  Thanks, I learned a  lot.

    Wednesday, January 10, 2018 10:08 PM
  • Hi,

    Yes, it is Leo. Glad to hear you got it to work. Good luck!

    Wednesday, January 10, 2018 10:11 PM
  • In this way you can easily make all your own "DLookUp's".

    The code is not tested, but you the general approach should be clear.

    Hi tkosel,

    I realized that the above code was for the fixed field name "SampleSize". You can generalize this using the next code:

    Function Get_samplesize(fieldname As String, AQL As Single, Lotsize As Long)
      Dim cur_sql As String
      
      cur_sql = "SELECT " & fieldname & " AS Veld FROM <SizeTable>" _
              & " WHERE AQL = " & AQL _
              & " AND LotSizeLower <= " & Lotsize _
              & " AND LotSizeUpper >= " & Lotsize
      Get_samplesize = CurrentDb.OpenRecordset(cur_sql)!Veld
    
    End Function
    

    The functionality is very / almost the same as the DLookup function. The difference comes from using more complex sql-strings including
    JOIN's.

    Imb.

    Wednesday, January 10, 2018 10:21 PM