Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: If Statement

  1. #1
    Join Date
    May 2004
    Posts
    55

    Unanswered: If Statement

    I want to use the result of a query to display MsgBoxes..I have a query "SCHEDULE" which compares data from two different tables...This is what I want to do:

    If the query results in a null value MsgBox "USED" Else MsgBox" PROCEED"

    I don't know how to set the query result as a part of my 'if' statement. Could someone help me with this??

    Thanks,
    DH

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    IIF(ISNULL(columnA), TrueValue, FalseValue)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by r123456
    IIF(ISNULL(columnA), TrueValue, FalseValue)

    I am not familiar with the database world..I tired the above statement but it gave me a syntax error...This is what I have..


    SELECT [HISTORY].[START_DATE]
    FROM HISTORY
    WHERE (((Exists (select null from SCHEDULE where START_DATE = HISTORY.START_DATE))<>False)) //This part compares the two tables and it works..
    Where does your statement go?

    IIF(ISNULL(HISTORY.START_DATE), YES, NO);

  4. #4
    Join Date
    May 2004
    Posts
    55

    Iff Statements...HELP!!!

    Could someone please help me with this. It is kind of urgent !!!!!

  5. #5
    Join Date
    May 2004
    Posts
    55

    Iff Statements...HELP!!!

    Could someone please help me with this..It is kind of urgent....Pleaseeee!!

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by dmesh
    Could someone please help me with this..It is kind of urgent....Pleaseeee!!
    alright, first things first. The design of your query is VERY bizarre. The query is going to return null every time. "SELECT NULL FROM [YOU CAN PUT ANYTHING HERE]" will ALWAYS return null, no matter what. It's like saying "SELECT 5" it will always return 5.

    what is it that you need to return exactly? Do you want to return the count of records from HISTORY that don't have matching records in SCHEDULE? What is the purpose of the query? I think it needs to be revamped...

    the next issue is displaying the msgbox. What context do you need to do this in?

    Depending on your answers to these questions, you may be able to do something like this:
    Code:
    If yourQuery.RecordCount = 0 Then
       Msgbox "Used"
    Else
       MsgBox "Proceed"
    End If
    Alternately, depending on what you're doing, you may need to restrict the user from proceeding until they take a certain action. I'm just guessing here, judging from the strings you'd like to return.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Teddy
    alright, first things first. The design of your query is VERY bizarre. The query is going to return null every time. "SELECT NULL FROM [YOU CAN PUT ANYTHING HERE]" will ALWAYS return null, no matter what. It's like saying "SELECT 5" it will always return 5.

    what is it that you need to return exactly? Do you want to return the count of records from HISTORY that don't have matching records in SCHEDULE? What is the purpose of the query? I think it needs to be revamped...

    the next issue is displaying the msgbox. What context do you need to do this in?

    Depending on your answers to these questions, you may be able to do something like this:
    Code:
    If yourQuery.RecordCount = 0 Then
       Msgbox "Used"
    Else
       MsgBox "Proceed"
    End If
    Alternately, depending on what you're doing, you may need to restrict the user from proceeding until they take a certain action. I'm just guessing here, judging from the strings you'd like to return.

    This is what I want to do:
    I am creating a database that will help users reserve rooms for meetings. Currently, I have a History Table which has a history of all the reservations.
    I have another table SCHEDULE which is used everytime a user wishes to make a new reservation. I use the START_DATE from both HISTORY and SCHEDULE and compare the values. This is done by the following query

    1)SELECT [START_DATE]
    FROM SCHEDULE
    WHERE START_DATE=[Forms]![RESERVATION]![START_DATE];

    2)SELECT [HISTORY].[START_DATE]
    FROM HISTORY
    WHERE (((Exists (select null from SCHEDULE where START_DATE = HISTORY.START_DATE))<>False));

    It does work though. If I have a date in the SCHEDULE table that is also in HISTORY, the query results shows all the records with that date.
    If a user selects a date that is already used I want to display a message "USED". If not , just go ahead and display "SUCCESSFUL RESERVATION". What do you think of this? Is this a good design?

    If I can compare and display messages, I would like to do the same for START_TIME, END_TIME and other fields in a table. In other words, I can't have two reservations at the same time. Hope this makes sense.

    Thanks,

    DH

  8. #8
    Join Date
    Jun 2004
    Posts
    96
    Hi, successful reservations are commited to the history table directly OR reservations are made on the schedule table, and committed to the history table only when the schedule is renewed (at a later stage)?

    basically you work either on history or schedule tables.

    1. reservation made on schedule table:
    check only if dates are reserved (dates requested are within schedule date range).
    2. reservation commit to history directly:
    check if dates are found on history table (requested dates are within schedule date range).

    It would be better if you can provide more details on how your database/tables are structured..

    Cyherus

  9. #9
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Cyherus
    Hi, successful reservations are commited to the history table directly OR reservations are made on the schedule table, and committed to the history table only when the schedule is renewed (at a later stage)?

    basically you work either on history or schedule tables.

    1. reservation made on schedule table:
    check only if dates are reserved (dates requested are within schedule date range).
    2. reservation commit to history directly:
    check if dates are found on history table (requested dates are within schedule date range).

    It would be better if you can provide more details on how your database/tables are structured..

    Cyherus
    Cyherus-- Basically, whenever the user enters the START_DATE for a new reservation it is inserted into the SCHEDULE table where ID=1.
    If the date entered is valid or one that is not used already the HISTORY table is updated with the new reservation date.
    The table structure is pretty simple . Both the tables have fields like START_DATE, END_DATE, START_TIME, END_TIME, USER, ITEM_NAME

    Does this help??

  10. #10
    Join Date
    Jun 2004
    Posts
    96
    what do you mean "it is inserted into the SCHEDULE table where ID=1".. your schedule table only has 1 record all the time? schedule is only used to hold the 'booking' information?
    I thought schedule is to hold/display the availability of the rooms for booking, while history stores the historical booking details of the rooms.

    Cyherus

  11. #11
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Cyherus
    what do you mean "it is inserted into the SCHEDULE table where ID=1".. your schedule table only has 1 record all the time? schedule is only used to hold the 'booking' information?
    I thought schedule is to hold/display the availability of the rooms for booking, while history stores the historical booking details of the rooms.

    Cyherus
    Yep..Schedule is only used to hold the booking information..and if the date is valid the HISTORY table gets updated..This way I can compare the dates..Does it sound okay?

  12. #12
    Join Date
    Jun 2004
    Posts
    96
    Thatz redundant (but not critical).. if you are using form, the booking form need not be bound to any tables and once user wants to 'book' (click button) you can check if the start_date, start_time can be found in the History table, if yes, tell the user that the room is already booked, else commit the details into the History table and prompts user that booking is successful.

    why do you need to have another schedule table and go through the hassle to create a query to match the details between the 2 tables.

    Cyherus

  13. #13
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Cyherus
    Thatz redundant (but not critical).. if you are using form, the booking form need not be bound to any tables and once user wants to 'book' (click button) you can check if the start_date, start_time can be found in the History table, if yes, tell the user that the room is already booked, else commit the details into the History table and prompts user that booking is successful.

    why do you need to have another schedule table and go through the hassle to create a query to match the details between the 2 tables.

    Cyherus
    I tried that earlier but I don't know the right syntax..Is it

    If Forms!RESERVATION!START_DATE = Tables!HISTORY.START_DATE Then MsgBox" " Else MsgBox ""

  14. #14
    Join Date
    Jun 2004
    Posts
    96
    dmesh, no, you have to look and search in the table for the record. simpler way would be to use Dlookup:

    dim var as variant
    var = DLookup("[ID]", "History", "[Start_Date] = #" & Forms!RESERVATION!start_date & "#")
    if isnull(var) then
    "record not found, so you can add and reservation will be successful"
    else
    "record found, so you cannot make reservation"
    endif

    You should find a lot of help in Access regard dlookup.

    Cyherus

  15. #15
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Alternatively you can insert the values and check the return value from the ADO insert function.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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