Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    check Q2 is returning values for that name - as as previously advised 'name' is about the worst reserved word you can use for a field name - although the revised code worked for me

  2. #17
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    its work now.
    Thank you guys

  3. #18
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by pio92 View Post
    its work now.
    Thank you guys
    Please mark the thread SOLVED. See link in page header.

  4. #19
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    I need help with:


    Code:
    UPDATE Table1 SET Table1.res = DLookUp("F6","Q3",CRITERIA);
    I need to add 3 criteria
    F7 = Table1.F7
    F14 = Table1.F14
    stat IS NULL

    I have tried several times, but it doesnt work.
    I guest I use incorrect: " ' &
    Can you help me pls?

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    show what you have tried

    what does 'doesn't work' mean?

  6. #21
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    Quote Originally Posted by Ajax View Post
    show what you have tried

    what does 'doesn't work' mean?
    Also if I use:

    Code:
    UPDATE Table1 SET Table1.res = DLookUp("F6","Q3","F14='" & [mainTable].[F14] & "'");
    it's work and update me 173 records

    I want two extra criteria:
    F7=[Table1].[F7]
    stat is NULL

    With the criterias its should be updating -in this case- 173 records too.

    I try for example with:
    Code:
    UPDATE Table1 SET Table1.res = DLookUp("F6","Q3" ,"[F7] = " &  [Table1].[F7] & " And [F14] = " &  [Table1].[F14] & " And [stat] = " &  "IS NULL" & "'");

    Error:
    Code:
    Microsoft Access can't update all the records in the update query.Microsoft Access didn't update 1565 fields(s) due to type [...]

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    in your example, you dropped the single quotes but you cannot

    "F14='" & [mainTable].[F14] & "'"

    put them back and you might be OK.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #23
    pio92 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    19
    Quote Originally Posted by Micron View Post
    in your example, you dropped the single quotes but you cannot

    "F14='" & [mainTable].[F14] & "'"

    put them back and you might be OK.
    Thanks. 50% work , i have problem with third criteria .

    Code:
     DLookUp("F6","Q1" ,"[F7] = " &  [Table1].[F7] & " And [F14] = '" &  [Table].[F14] & "'" ); 
    its works perfect (thougt I use single quotes only by F14, F7 without ')

    but if I add third criteria stat IS NUL my table isnt updates:
    Code:
     DLookUp("F6","Q1" ,"[F7] = " &  [Table1].[F7] & " And [F14] = '" &  [Table].[F14] & "' AND [stat] IS NULL" ); 
    Code:
     DLookUp("F6","Q1" ,"[F7] = " &  [Table1].[F7] & " And [F14] = '" &  [Table].[F14] & "' AND [stat] '" IS NULL "' "); 
    I try with difference adding " in different position but still doesnt work

  9. #24
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe [stat] is not null, but is a zero length string (zls, aka "" ) OR there are no records with Nulls.
    To test that, create a query using that table and field and set Is Null as the criteria. If it returns no records, change Is Null to "" in the criteria row. If you get records, the blanks are not Nulls but are empty strings.

    Could also try "' AND IsNull([stat])" but "' AND [stat] IS NULL" should work IF there are records with Nulls.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update table based on closest match on date
    By dalahans in forum Queries
    Replies: 6
    Last Post: 06-07-2018, 07:50 AM
  2. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  3. Replies: 2
    Last Post: 08-01-2017, 01:40 AM
  4. Replies: 2
    Last Post: 09-03-2015, 04:38 PM
  5. Trouble finding closest match
    By cutsygurl in forum SQL Server
    Replies: 1
    Last Post: 02-22-2013, 03:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums