Go Back   Access World Forums > Search Forums

 
Showing results 1 to 25 of 500
Search took 0.37 seconds.
Search: Posts Made By: plog
Forum: Queries 06-21-2019, 12:36 PM
Replies: 2
Views: 129
Posted By plog
Re: Assign % based on EARNED

Use the designer as if you are building a regular query. Bring down all the fields from the Crew table, the PCNT field from that table. Make it an aggregate query (click the Sigma/Summation symbol...
Forum: Queries 06-13-2019, 05:43 AM
Replies: 5
Views: 92
Posted By plog
Re: Pulling two queries back into one

I bet you can do this with just 2 queries, but for simplicity sake, I suggest 3:

Q1--this exactly the Query 1 you have now.

Q2--this would be based on Table1 and predict...
Forum: Tables 06-04-2019, 05:54 PM
Replies: 9
Views: 245
Posted By plog
Re: Many to many relationship but with exceptions

Ugh. When you want to discuss technical issues, especially with people not familiar with your technical details you can't casually reference items.

Your Initial post referenced "SystemVersions"...
Forum: Tables 06-03-2019, 10:49 AM
Replies: 9
Views: 245
Posted By plog
Re: Many to many relationship but with exceptions

That doesn't make sense. Normally ( and in this case) junction tables aren't real things, unlike other tables that represent real life entities, junction tables exist to sort out data between other...
Forum: Queries 05-08-2019, 12:08 PM
Replies: 5
Views: 117
Posted By plog
Re: Hide Query Results

Why? This sounds like you've set up your database incorrectly. You don't store calculated values in a table, instead you calculate them in a query and reference the query when you need that value....
Forum: Queries 04-23-2019, 05:03 AM
Replies: 7
Views: 169
Posted By plog
Re: Update Value in table

Nope. You need to build your database correctly. You've just created a point of failure for your data. In one year post your database and I will lay odds that not all calculated totals in table 1...
Forum: Queries 04-18-2019, 09:52 AM
Replies: 56
Views: 866
Posted By plog
Re: The Val function

1. Structure your tables properly. When you start numbering field names, its time for a new table. Instead of 9 fields to store these values, you need a new table to which you add a new record for...
Forum: Tables 04-16-2019, 08:56 AM
Replies: 15
Views: 314
Posted By plog
Re: Boolean as Null

Excellent code, I got the same results as isla and now see your point.
Forum: Tables 04-16-2019, 08:04 AM
Replies: 15
Views: 314
Posted By plog
Re: Boolean as Null

I think Doc just mistyped that. His overall message is "don't trust Null", which is correct.

Null <> Null resolves to False in Access:

TestTable:
id, a, b
1, 1, 1
2, 1, Null
3, Null,...
Forum: General 04-09-2019, 12:52 PM
Replies: 3
Views: 371
Posted By plog
Re: Advice needed Tricky Dates

Working with dates is never clean when you use more than one unit. Years don't have a definite amount of days, so you need to decide what constitutes a full year, program accordingly and accept...
Forum: Queries 04-09-2019, 06:59 AM
Replies: 25
Views: 852
Posted By plog
Re: Syntax Error in SQL

Ampersands are how you connect parts of a string together. You can build strings with literals (text between quote marks, "This is a literal") and variables.

Variable = "a variable"
"This is a...
Forum: Queries 04-09-2019, 06:21 AM
Replies: 25
Views: 852
Posted By plog
Re: Syntax Error in SQL

In VBA no, in SQL yes.

In SQL when inserting a string you should put single quotes before and strings:

"WHERE StringField='" & StringVariable & "'"
"WHERE NumberField=" & NumberVariable
Forum: Queries 04-09-2019, 06:11 AM
Replies: 25
Views: 852
Posted By plog
Re: Syntax Error in SQL

No it will not. You are not treating intPartID as a variable. It will be interpreted as a field in tblParts.

You need to stop the string, insert the value of the variable:

TheVariable = 17
...
Forum: Queries 04-09-2019, 04:58 AM
Replies: 25
Views: 852
Posted By plog
Re: Syntax Error in SQL

After you fix the syntax error that thing still shouldn't work. You are improperly grouping your data. Here's the rule:

In a query with a GROUP BY, every field in the SELECT must either be in...
Forum: Queries 04-05-2019, 04:54 AM
Replies: 2
Views: 130
Posted By plog
Re: Simplify Multiple Joins for Sub

As long as the fields in the query don't have similarly named counterparts in the other tables in the query you can omit the table portion when referencing fields:

SELECT InspectionEvent_PK,...
Forum: Forms 04-03-2019, 07:37 AM
Replies: 23
Views: 416
Posted By plog
Re: Help with Order form design

First the simple rules about tables:

No spaces in names. It just makes coding and querying harder down the line. Simply remove them (Customer Job Locations -> CustomerJobLocations)

No...
Forum: Queries 04-02-2019, 04:59 AM
Replies: 7
Views: 249
Posted By plog
Re: To count records in a query

That can be accomplished with a simple aggregate query:


SELECT CategoryNumber, COUNT(CategoryNumber) AS TotalRecords
FROM YourTableNameHere
GROUP BY CategoryNumber;


But this part doesn't...
Forum: General 04-01-2019, 12:51 PM
Replies: 6
Views: 184
Posted By plog
Re: Store Value from unbound combo box

There only needs to be a DivisionID in the attorney detail table.

Since a DivisionID is associated with a SectionID in tblSectionsDivisions you know what section an attorney goes to if you know...
Forum: Queries 03-22-2019, 08:54 AM
Replies: 2
Views: 109
Posted By plog
Re: update data on sql table using vba

You have 36 double quotes in that thing. You have 33 single quotes. Somebody is missing a partner.

Further, why isn't your WHERE clause finished?
Forum: Queries 03-22-2019, 08:12 AM
Replies: 8
Views: 182
Posted By plog
Re: Reset Rank column in MS Access query every year

You need to add in a year criterion in your DCount, not inside it:


SELECT DCount("DDate","MyTable","DDate<=#" & [DDate] & "# AND Year(DDate)=" & Year([DDate])) AS Rank, MyTable.DDate
FROM...
Forum: Queries 03-14-2019, 06:16 AM
Replies: 7
Views: 114
Posted By plog
Re: Need help with queries that span tables.

Follow MajP's example when building your UNION. The thing to remember is that the UNION should simply bring all your data together into one query object.

--Don't apply criteria to it
--Don't try...
Forum: Queries 03-14-2019, 05:15 AM
Replies: 7
Views: 114
Posted By plog
Re: Need help with queries that span tables.

Why? This isn't the proper way to house data. Why isn't all the Sales_Master data housed together properly?

I don't understand your example's in parenthesis. Are those row names or values in a...
Forum: Queries 03-12-2019, 12:30 PM
Replies: 16
Views: 261
Posted By plog
Re: Trim Funtion from HELL

Correct. That's why I suggested a custom function in a module.
Forum: Queries 03-12-2019, 08:17 AM
Replies: 16
Views: 261
Posted By plog
Re: Trim Funtion from HELL

Now its time for a custom function. You create a module and in it write a function to do that. I would use the split function (https://www.techonthenet.com/access/functions/string/split.php) and...
Forum: General 03-08-2019, 11:51 AM
Replies: 14
Views: 322
Posted By plog
Re: Click button to retrieve Now()

The input itself is clickable. So the short answer is just add the code to the OnClick event of that control.

You could also add a button, lock down the field and update it whenever its...
Showing results 1 to 25 of 500

 
Forum Jump



All times are GMT -8. The time now is 04:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World