Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Code for Checking if a Book serial exists between a given range

    I have several forms from which I want to capture different farmer related data i.e. farmer registration, farmer input sales, farmer product sales etc. Besides their respective source tables (tblFamerRegister, tblInputSales and tblProductSales) I have also created a table named tblserialcontrol with the following fields;- StartRange, EndRange and SerialType (the serialtype being the names of the different serial books i.e. farmerRegister, InputSales or ProductSales).

    As I capture say, the SalesNo on my Input sales form, I want Access to check if that number falls between the [StartRange] & [EndRange] of one of the ranges with SerialType 'InputSales' in tblSerialControl.

    Could somebody guide me into writing a code that will help me achieve this objective?

    Thanks a lot in advance

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I write functions (saved in a module) to do things like this.

    For example (only - tweak as you need), you might have a function as such (this example uses 2 date fields):

    Function IsDateOk(SDatePassed as date, EDatePassed as date) as boolean
    IsDateOk = false 'originally assuming it to not be ok.
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where MyStartDateField => #" & SDatePassed & "# and MyEndDateField <= #" & EDatePassed & "#"
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then 'Date doesn't fall within other dates in recordset.
    IsDateOk = true
    else
    IsDateOk = false
    end if
    rs.close
    set rs = nothing
    End Function

    and then you'd call the function as such...

    if IsDateOk(me!MyStartDateField, me!MyEndDateField) = true then
    msgbox "Date is ok."
    else
    msgbox "Date is not ok."
    end if

    You can then call the function anywhere within your design to check if the date is ok. But Importantly Note: Both the MyStartDateField and MyEndDateField must be a date field type. If you're only checking on 1 date field versus 2 date fields such as a startdate and enddate, again, modify the function as needed. The point being that you can utilize functions which are passed values (any kind of values) to check the logic within the function and return (either a value or True/False). You can then re-utilize the same function anywhere on any form. They will save you many hours of re-writing code.

    Also, if you have other criteria such as checking for a specific SerialType along with the dates, add that as a variable in the function to be passed and apply it within your strSQL statement. In some cases, you may need to loop through the recordset in the function to determine if IsDateOK should return true or false. Again, the above is just an example of many ways to do it and you'll need to think through how you want to design the function to meet your needs (ie. don't just copy the above code and expect it to work for your specific situation).

    Another alternative is to design a query and utilize something such as a dlookup command to check your logic.
    Last edited by pkstormy; 04-24-10 at 11:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2010
    Posts
    10
    Thanks for the quick feedback. I will just have to sweat abit in ensuring that this code works for a number datatype field since my serials are all numbers. Where I get stuck, I will shout for more help.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can again, tweak the function to meet whatever logic for number types as well. It becomes more difficult though if you're actually trying to utilize numbers and finding that within a date range. If looking through a recordset for date ranges, it's highly recommended you have some kind of date type field. Otherwise it can beecome a bit more difficult.

    Or again, you can simply utilize the dlookup command if you can make the logic work against the table or a query since the dlookup command has criteria you can put within it. I use functions since dlookup can become a bit slow with mass records.
    Last edited by pkstormy; 04-25-10 at 08:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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