Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    makes table with dates chosen by the user (Access 2000)

    Make table with dates chosen by the user


    I have copied from a book a very nice looking form containing a calendar and 2 controls, named
    txtBeginDate and txtEndDate. When i choose a date from the calendar, for example 06.02.2001, this date appears on the control,and then the same for the txtEnddate.
    I have a maketable function and i want very much to tie up my function with the calendar,but i do not know why,if possible at all.

    My maketable function is the folowing:
    Dim strOrders As String
    Dim strWhere As String

    strWhere = " WHERE ((([orders].[orderdate]) between >#1/1/2001# And #1/2/2001));" ' the setting is dd.M.yyyy
    ' ====================================

    strOrders = " SELECT orders.orderid, orders.customerid, orders.orderdate, orders.[required date], orders.SalesTaxRate, orders.freigth,

    orders.paymentid, orders.PaymentMethodID, orders.bankid, orders.FreightCharge, orders.invoicedate, orders.AuftragNr INTO orders1

    FROM orders"

    CurrentDb.Execute strOrders & strWhere
    End Function

    My question is, can i pass the values chosen in the controls txtBeginDate and txtEndDate into my maketable function,
    in order to obtain a new table based on the values i have chosen in the calendar form.If i succedd i could gain a big flexibility
    for the user to choose dates.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: makes table with dates chosen by the user (Access 2000)

    Yes, but how you do it depends on exactly what you want to do. If you want to call the function from the form containing the calendar control, it would be simple to pass the two dates as arguments into your function. An alternative would be to call the function while the form is open, either hidden or otherwise, and refer to the two textboxes to get the date values.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: makes table with dates chosen by the user (Access 2000)

    Thank you very much for your kind reply.I have tried to substitute the controls in my function,but still
    i receive the error 3075 missing item in query expression.

    What have i done.In my working strWhere expression
    strWhere = " WHERE ((([orders].[orderdate])>#1/1/2001#));"
    i have replaced the right side part of >#1/1/2001#)) with between Forms![frmDateRange![BeginDate] And

    Forms![frmDateRange![EndDate

    So my new strWhere looks like this, but does not work:

    strWhere = " WHERE ((([orders].[orderdate]) between >#" & txtBeginDate & "# And #" & txtEndDate & "#));"


    So my mistake lies somwehere in the above code.I cannot understand.

    My previous working function was the following

    Public Function FncRecentTables()
    Dim strOrders As String
    Dim strOrderDetails As String
    Dim strWhere As String
    '******************** note that the strWhere is valid for all the tables so it could be changed only once

    strWhere = " WHERE ((([orders].[orderdate])>#1/1/2001#));" ' the setting is dd.M.yyyy
    ' ====================================

    strOrders = " SELECT orders.orderid, orders.customerid, orders.orderdate, orders.[required date], orders.SalesTaxRate, orders.freigth,

    orders.paymentid, orders.PaymentMethodID, orders.bankid, orders.FreightCharge, orders.invoicedate, orders.AuftragNr INTO orders1

    FROM orders"
    CurrentDb.Execute strOrders & strWhere
    End Function
    I simply cannot find my way out of this problem


    May i wish you a merry Christmas

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: makes table with dates chosen by the user (Access 2000)

    Hi,
    Try removing the > sign after the word 'between' - so your line should read:

    strWhere = " WHERE ((([orders].[orderdate]) between #" & txtBeginDate & "# And #" & txtEndDate & "#));"

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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