Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: If statement in In a sql query

    I have a SQL query Generating 2 Random numbers, the first pulls a number 100 - 999, the Second currently pulls a number 1-500.

    The problem, I need to insert an if statement to Verify against an ID # in the main table, if the number does not exist I need the random generator to run and pull a new number.

    The Code.
    Code:
    DoCmd****nSQL "SELECT Int((999-100+1)*Rnd()+100) AS RanNum, Int((500-1+1)*Rnd()+1) AS RanID INTO tbl_Temp;"

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    I'd suggest creating a function to generate your number and just add that to your query.

    Code:
    Function GetRanNum() As Integer
    GetRanNum = Int((999 - 100 + 1) * Rnd() + 100)
    End Function
    Function GetRanID() As Integer
    GetID:
    GetRanID = Int((500 - 1 + 1) * Rnd() + 1)
    'Do your testing and if number does not exist in main table then Goto GetID
    End Function
    Function InputRan()
    DoCmd****nSQL "SELECT " & GetRanNum & " AS RanNum, " & GetRanID & " AS RanID INTO tbl_Temp;"
    End Function

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Thanks for the Assistance, I was working on it last night and I what I ended up doing was by passing the If statement and created a variable that will be the upper limit for the second number, in this case instead of 500 I would like it to use the variable. Everything is working up to the 2nd half of the sql query. I have a msgbox that verifies its pulling the information correct, but then it breaks trying to get the sql to read it.

    Tried this:
    Code:
    Dim varIDcount As String
    Dim SQL As String
    varIDcount = (Forms!frm_test!txt_ManNum)
    MsgBox (varIDcount)
    
    SQL = "SELECT Int((999-100+1)*Rnd()+100)AS RanNum," & _
    "Int((varIDcount-1+1)*Rnd()+1)" & _
    "AS RanID INTO tbl_Temp;"
    
    DoCmd****nSQL SQL
    And This:
    Code:
    Dim varIDcount As String
    Dim SQL As String
    varIDcount = (Forms!frm_test!txt_ManNum)
    MsgBox (varIDcount)
    
    SELECT Int((999-100+1)*Rnd()+100) AS RanNum, Int((varIDcount-1+1)*Rnd()+1) AS RanID INTO tbl_Temp
    Last edited by Technodruid; 08-02-13 at 17:01. Reason: added Code

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    try:
    Code:
    SELECT Int((999-100+1)*Rnd()+100) AS RanNum, " & Int((varIDcount-1+1)*Rnd()+1) & " AS RanID INTO tbl_Temp

Posting Permissions

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