Search results

Hi - Try playing with this. Example from the debug (immediate) window: ? format(date(), "dddd") & ", " & day(date())& Nz(Choose(IIf(day(date()) < 21, day(date()), day(Date()) Mod 10), "st", "nd", "rd"), "th") & " of " & format(date(), "mmmm") Tuesday, 15th of December Realize it's a tad...

Hi - Please post the query-SQL for your report. What I provided was intended only as an easy way to create date parameters by entering only mm/yyyy, e.g. 12/2009. Bob

Hi - In your report footer, add a text box. In Properties: 1) Show Format blank 2) Under Data, show control source as: =Format(Str(Sum([Total])*100),"0000000000"). ...assuming your field is [Total]. Correct as necessary. As seen in my previous example, this will return up to a 10 character...

Hi - Try playing with this: x = 1234.56 y = format(str(x * 100), "0000000000") ? y 0000123456 HTH - Bob

Hi - Provided you're looking for complete months, this statement placed in the criteria cell of your query's date field will return a Between statement listing the start and end dates of the month. Note that it does involve one parameter pop-up but there's no need to compute the start and end...

Hi - Welcome to the forum. Take a look at this code posting. It will allow you to search for multiple parameters. HTH - Bob

Hi - Give this a try. Public Function RoundTo(pItem, pfactor) 'Purpose: Round to the nearest pfactor 'input: from immediate window: ? roundto(15.35, 0.25) 'output: 15.25 RoundTo = (Int(pItem / pfactor + 0.5) * pfactor) End Function HTH - Bob

Hi - Take a look at DCrake's time selector. Some really good stuff here which you may be able to adapt to your needs. Bob

Hi - Try playing with this: Wk = 24 WD = 4 Yr = 09 x = dateadd("ww", wk, dateserial(yr, 1, 1)) y = x - iif(weekday(x)<> wd, weekday(x), 0) + iif(weekday(x)<> wd, wd, 0) ? y 6/17/2009 I didn't test for all possibilities but it should give you a starting point. Best Wishes - Bob

Hi Marie - Here's a working example based on Northwind's Orders table: SELECT DatePart("q",[OrderDate]) AS TheQtr , Orders.CustomerID , Count(Orders.CustomerID) AS CountOfCustomerID FROM Orders GROUP BY DatePart("q",[OrderDate]) , Orders.CustomerID HAVING ((Not...

Hi - As an alternative, you might try the Switch() function, e,g, x = 16 ? switch(x<14, "Cadet", x<=18, "Junior", True, "Senior") Junior HTH - Bob

Hi - Here are a couple of examples that return the correct year: x = #4/13/2010# ? Year(x) - IIf(x < DateSerial(Year(x), 4, 1), 1, 0) 2010 x = #3/13/2010# ? Year(x) - IIf(x < DateSerial(Year(x), 4, 1), 1, 0) 2009 HTH - Bob

OK. Very strange although I see what you're saying (worked perfectly in my tests) Do the following: 1) In table design for table2, ensured that Indexed is set to No for each of the two fields. 2) Change the function call to: ? TransposeRecordset("Table1", "Table2", "Field1") Bob

Hi - Try this: 1) Create Table2 with two fields: Product, OptionCode 2) Copy the below code to a standard module, make sure the module and function names are not duplicates. 3) Call the function from the debug/immediate window, i.e. ? TransposeRecordset("Table1", "Table2", "Product")...

Hi - I'm confused. Is that true? Do you perhaps mean over 100 records. Could you provide an sample of Table1. If so, please save as an .mdb (pre-A2007) Bob

Hi - If your question is an add-on to this post, try adding a calculated field to a query: Expr1: IIf(InStr([Fld2],"XYZ")>0 Or InStr([Fld3],"XYZ")>0 Or InStr([Fld4],"XYZ")>0,"Yes","No") HTH - Bob

Re: "In Between" function that behaves similarly to a "Mid String" function You're most welcome. Best Wishes - Bob

Great! Glad it helped. Best Wishes - Bob

Hi - Try this as the criteria for your date field: Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1 Run the query and, when prompted enter mm/yyyy (e.g. 12/2009). Should return all records for the input month. HTH - Bob

Hi - Try this select query, replacing table and field names as necessary. When satisified, convert it to an append query. SELECT Field1 , Left([Field1],3) AS Fld1 , IIf(Len([Field1])>3,Mid([Field1],4,3),"") AS Fld2 , IIf(Len([Field1])>6,Mid([Field1],7,3),"") AS Fld3 ...