# Search:

Type: Posts; User: stumac; Keyword(s):

Page 1 of 6 1

1. ## Thread: AccesS Formula help

by stumac
Replies
5
Views
206

### Re: AccesS Formula help

I find the switch function is a clearer and neater option for things like this. If DaysAged returns and integer then:

Switch([DaysAged]<31,"<30 Days",[DaysAged]<61,"31-60...
2. ## Thread: Access: IIf formula missing a bracket, parenthesis...

by stumac
Replies
8
Views
442

### Re: Access: IIf formula missing a bracket, parenthesis...

I am not sure what your Dsum is meant to achieve, you are only filtering out zeros with the month active filter - so it will sum (which is the same as counting in this case) ALL the 1's in the query....
3. ## Thread: Access: IIf formula missing a bracket, parenthesis...

by stumac
Replies
8
Views
442

### Re: Access: IIf formula missing a bracket, parenthesis...

:eeek::eeek:

Oops!

IIf(DateDiff("m",[PDIR Due Date],Now())<>0 Or DateDiff("m",[PDIR Submission Date],Now())<0 Or DateDiff("m",[PDIR Completed],Now())>0,0,1)
4. ## Thread: Access: IIf formula missing a bracket, parenthesis...

by stumac
Replies
8
Views
442

### Re: Access: IIf formula missing a bracket, parenthesis...

Agree with Joe, you are using Cstr and then comparing to a number, I would take that out. You can also reduce to one IIF if you switch from AND to OR:

IIF(DateDiff("m",[PDIR Due Date],Now())<>0...
5. ## Thread: Retrieve last row Value from Access Table

by stumac
Replies
5
Views
748

### Re: Retrieve last row Value from Access Table

If you have an autonumber or date field then you could also combine Dmax with Dlookup to get the true last. If there is no additions or deletions then DLast will get the last entry.
6. ## Thread: Convert Excel Formula To Access

by stumac
Replies
5
Views
694

### Re: Convert Excel Formula To Access

Similar with your Excel formula the second IF and the AND function are not required. If can return one value if true and another if false. IF,Then,Else.

=IF(S2>1825,">","<")&" 5 Years Old"
7. ## Thread: .CSV Email issue

by stumac
Replies
5
Views
121

### Re: .CSV Email issue

You could try adding in a friendly name -

by stumac
Replies
5
Views
121

9. ## Thread: New table by grouping fields

by stumac
Replies
4
Views
1,143

### Re: New table by grouping fields

Realised that your data is probably in different fields (not one field separated by commas), the code above wont work as is for that. If that's the case and the fields are in the correct order (as...
10. ## Thread: New table by grouping fields

by stumac
Replies
4
Views
1,143

### Re: New table by grouping fields

Just had a thought - you would need to watch out for commas that don't act as a delimiter - i.e. in a comment.
11. ## Thread: New table by grouping fields

by stumac
Replies
4
Views
1,143

### Re: New table by grouping fields

It can be done with VBA - in the code below I have highlighted the fields you would change to suit your DB. In the example the data would be in a table called MyTable within a field called MyField...

by stumac
Replies
2
Views
1,096

One way using SQL would be to combine 4 Select top N queries then group them:

SELECT Prd_code, Prd_desc, Cat_code, Cat_desc, MAX(Q1_Sales) AS xQ1_Sales, MAX(Q2_Sales) AS xQ2_Sales, MAX(Q3_Sales)...
13. ## Thread: SQL statement is give me error 3061 Too few parameters. Expected 1

by stumac
Replies
20
Views
2,948

### Re: SQL statement is give me error 3061 Too few parameters. Expected 1

You haven't given us all of the code, only the bit where you are building a query string.

Is the report data source being set to strSQL?
14. ## Thread: SQL statement is give me error 3061 Too few parameters. Expected 1

by stumac
Replies
20
Views
2,948

### Re: SQL statement is give me error 3061 Too few parameters. Expected 1

assuming the code is triggered by an event on the same page as the combo box:

strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
& "FROM...
15. ## Thread: Report issue suppressing page Header and Footer on report footer

by stumac
Replies
4
Views
1,247

### Re: Report issue suppressing page Header and Footer on report footer

I don't understand why a page header or footer is relevant in the Report View, typically you would display repeating headers for printed pages, page numbers etc on these. In report view there are no...
16. ## Thread: Report issue suppressing page Header and Footer on report footer

by stumac
Replies
4
Views
1,247

### Re: Report issue suppressing page Header and Footer on report footer

In report View mode the idea of pages doesn't really exist - i.e. its one big report. I tend to open the reports for users in Print Preview mode so they see the paginated report as the would if it...

by stumac
Replies
3
Views
1,416

Add 2 unbound controls to your report and set the the same way you are setting your GroupLevel:

You will need to change the name of the controls to match what you name your unbound controls.

...
18. ## Thread: SQL statement is give me error 3061 Too few parameters. Expected 1

by stumac
Replies
20
Views
2,948

### Re: SQL statement is give me error 3061 Too few parameters. Expected 1

VBA expects the parameter to be passed to it. Instead of having the query pop up and ask for the parameter, do it before hand. Also there is no need for your VbCrLF unless you are wanting to have...
19. ## Thread: Tring to get VB code to automatically scroll through a table.

by stumac
Replies
5
Views
1,319

### Re: Tring to get VB code to automatically scroll through a table.

So the code I posted in #2 will do what you need.

Depending on what your query is doing then I would question the need to run it 150 times. If you do need to do this then I would create a...
20. ## Thread: Tring to get VB code to automatically scroll through a table.

by stumac
Replies
5
Views
1,319

### Re: Tring to get VB code to automatically scroll through a table.

No problem, most of us on here are the same as you, figuring things out through experience :)

What is the macro you are running doing?
21. ## Thread: Tring to get VB code to automatically scroll through a table.

by stumac
Replies
5
Views
1,319

### Re: Tring to get VB code to automatically scroll through a table.

It looks like the code you have is for the oncurrent event rather than an event linked to a button. What you want can be achieved fairly easily, but its a strange approach to loop through a form and...
22. ## Thread: Combine multiple row into one with line breaks using sql

by stumac
Replies
4
Views
1,111

### Re: Combine multiple row into one with line breaks using sql

Dont think that would work, it would you show a maximum of 2 remarks on a row and each one would be duplicated A/B, B/A. If there was more than 2 then you would get each combination twice.

Also...
23. ## Thread: Combine multiple row into one with line breaks using sql

by stumac
Replies
4
Views
1,111

### Re: Combine multiple row into one with line breaks using sql

If you know the number of rows then you can achieve this using IIF statements, however, it is a messy solution - especially if its likely to change.

Otherwise its not possible using only SQL - you...
24. ## Thread: Delete Query Not Working because table not specified

by stumac
Replies
5
Views
1,168

### Re: Delete Query Not Working because table not specified

Glad you got it working. Both queries are essentially the same, just access adds in a bunch of things when you use the query builder.

When you say there are about 10 subsets, is that 10 queries...
25. ## Thread: Delete Query Not Working because table not specified

by stumac
Replies
5
Views
1,168

### Re: Delete Query Not Working because table not specified

AFAIK in SQL both access and other SQL variances you would need a FROM clause. You don't need to specify which fields (or *) in access as with other SQL:

Delete FROM [80- Section Backlog...
Results 1 to 25 of 150
Page 1 of 6 1