Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query help (Access2k)

    I have the following query which is supposed to display records according to the date I enter. It was working fine till yesterday but its not working today for some reason. I know there are records for the date I'm entering but it won't pick the records up. Someone PLEASE help me....

    PARAMETERS [Enter Trade Date dd/mm/yy] DateTime;
    SELECT Trade.tnum, Sec.ticker AS id, Sec.name, Sec.cusip, Sec.isin, Sec.sedol, Trade.clr2, Trade.tt, Trade.td, Trade.settles, Trade.tccy, Trade.q, Trade.tc, IIf([tccy]="GBP" Or [tccy]="GBp",[tp]*0.01,[tp]) AS NewPrice, Trade.tai, Trade.tax, Trade.moneyspot, Trade.notes, Trade.cancel, Trade.fund, Trade.splitref, TradeCpty.shortname, TradeCpty.cpty_name, Trade.ctc, DateDiff("ww",[ td ],[settles])*5+Weekday([settles])-Weekday([ td ]) AS Wk, nz(Switch([Wk]=2,"SPOT",[Wk]>=5,"FWD")," ") AS SF
    FROM Sec INNER JOIN (TradeCpty INNER JOIN Trade ON TradeCpty.cpty = Trade.cpty) ON Sec.id = Trade.id
    WHERE (((Trade.td)=IIf(nz([Enter Trade Date dd/mm/yy],0),[Enter Trade Date dd/mm/yy],Date())))
    ORDER BY Trade.tnum;

  2. #2
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2k)

    I think its caused by this:
    IIf(nz([Enter Trade Date dd/mm/yy],0),[Enter Trade Date dd/mm/yy],Date())
    Can anybody see anything wrong with this code??
    PLEASE <img src=/S/help.gif border=0 alt=help width=23 height=15>

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

    Re: query help (Access2k)

    I don't know what you're trying to do with this. You don't want a zero as a date criteria, there's no reason to use both IIf and Nz in the same expression, and what your expression is really saying is "If ([Enter Trade Date dd/mm/yy] or Zero) then use [Enter Trade Date...] Else use Date()".

    Try this:

    Nz([Enter Trade Date dd/mm/yy],Date())

    What this says is "If [Enter Trade Date ...] returns a null, use Date()".
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2k)

    This is my SQL with the criteria u suggested but it still doesn't work <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    SELECT Trade.tnum, Sec.ticker AS id, Sec.name, Sec.cusip, Sec.isin, Sec.sedol, Trade.clr2, Trade.tt, Trade.td, Trade.settles, Trade.tccy, Trade.q, Trade.tc, IIf([tccy]="GBP" Or [tccy]="GBp",[tp]*0.01,[tp]) AS NewPrice, Trade.tai, Trade.tax, Trade.moneyspot, Trade.notes, Trade.cancel, Trade.fund, Trade.splitref, TradeCpty.shortname, TradeCpty.cpty_name, Trade.ctc, DateDiff("ww",[ td ],[settles])*5+Weekday([settles])-Weekday([ td ]) AS Wk, nz(Switch([Wk]=2,"SPOT",[Wk]>=5,"FWD")," ") AS SF
    FROM Sec INNER JOIN (TradeCpty INNER JOIN Trade ON TradeCpty.cpty = Trade.cpty) ON Sec.id = Trade.id
    WHERE (((Trade.td)=Nz([Enter Trade Date dd/mm/yy],Date())))
    ORDER BY Trade.tnum;
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2k)

    Could it be because of the date format?? How can I make sure the computer reads the date as DD/MM/YY no matter what the date formt the pc is set to?

    YEP, it works but the date format varies to different machines, How can I convert the date to suit each machine??

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

    Re: query help (Access2k)

    Are you suggesting that the date is not mm/dd/yy on all machines? Are some of them a 4-year date or are you talking about a non-American date format like yymmdd or dd/mm/yy? You can't handle other date formats automatically in this SQL.

    And what do you mean "to suit each machine"? Are you talking transferring from another machine or are you talking about running the SQL from different machines? And is this, in fact, a date/time field in the first place?
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2k)

    Yes, its a Date & time feild.
    The database im working on is on a shared network so users access this program from different Pc's. The date format on each machine differs e.g dd/mm/yy, mm/dd/yy
    So I want to find a way of converting or telli9ng the user the current format so that they could enter the date in that format.

    Thank you for helping Charlotte.
    I appreciate it a lot.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

    Re: query help (Access2k)

    Don't even try. Dates are stored internally as numbers that represent the date with the decimal portion representing the time. It doesn't matter what the individual machine format is, dates are stored the same way, although they may appear differently.
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query help (Access2k)

    then why doen't it work when I enter date as 15/01/02 but works when i enter 01/15/02??

Posting Permissions

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